Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
changing cell format based on word in first cell of row
I would like to understand how I can go through my workbook
looking at the word in the first cell of column A and change the cell format in that row to add a pattern, example In the "A" column I have one of three words it's either "Closed" "Open" or "Waiting" If its "Closed" I would like to select cells "A" through "H" on that row only and format those cells by changing the color to White If its "Open" I need to select those same cells and format then by changing the color to Green If its "Waiting", I would like to change those cells to Yello What I have been doing is sorting the sheet by the first column the manually selecting the rows and columns and formatting those cells with a right click, format cells, pattern then selecting the color based on the word in "A" column If this could happen automatically when ever this word is chnaged that would be great or even if I could just run a macro to run through the sheet and update row by row that would work as well TIA Jeff W. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
changing cell format based on word in first cell of row
Use conditional formatting:
Select all columns A:H, use Format/Conditional Formatting Change "cell value is" to "formula is" Assuming A1 is the ACTIVE cell, enter this formula: =$A1="Closed" then click Format button, then select Patterns tab, then click the White font. Click OK, then click the Add button, enter =$A1="Open" then click Format button, then select Patterns tab, then click the Freen font. Click OK, then click the Add button, enter =$A1="Waiting" then click Format button, then select Patterns tab, then click the Yellow font. Click OK, then click OK. You're all set. "Jeff W." wrote: I would like to understand how I can go through my workbook looking at the word in the first cell of column A and change the cell format in that row to add a pattern, example In the "A" column I have one of three words it's either "Closed" "Open" or "Waiting" If its "Closed" I would like to select cells "A" through "H" on that row only and format those cells by changing the color to White If its "Open" I need to select those same cells and format then by changing the color to Green If its "Waiting", I would like to change those cells to Yello What I have been doing is sorting the sheet by the first column the manually selecting the rows and columns and formatting those cells with a right click, format cells, pattern then selecting the color based on the word in "A" column If this could happen automatically when ever this word is chnaged that would be great or even if I could just run a macro to run through the sheet and update row by row that would work as well TIA Jeff W. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
changing cell format based on word in first cell of row
There seems to be something wrong with this, I put all this in
and yes it did change some row colors but not what I expected So I deleted this, and just put in a single conditional format and I just selected all the cells and enterd one for "Open" which should turn all the cells with the word "Open" in the first cell green But what it did was it changed three row's at random one had "Closed" one had "Waiting" and one had "Open" Would this also work on new rows that I create during the day? Thanks... Jeff W. "Bob Umlas, Excel MVP" wrote in message ... Use conditional formatting: Select all columns A:H, use Format/Conditional Formatting Change "cell value is" to "formula is" Assuming A1 is the ACTIVE cell, enter this formula: =$A1="Closed" then click Format button, then select Patterns tab, then click the White font. Click OK, then click the Add button, enter =$A1="Open" then click Format button, then select Patterns tab, then click the Freen font. Click OK, then click the Add button, enter =$A1="Waiting" then click Format button, then select Patterns tab, then click the Yellow font. Click OK, then click OK. You're all set. "Jeff W." wrote: I would like to understand how I can go through my workbook looking at the word in the first cell of column A and change the cell format in that row to add a pattern, example In the "A" column I have one of three words it's either "Closed" "Open" or "Waiting" If its "Closed" I would like to select cells "A" through "H" on that row only and format those cells by changing the color to White If its "Open" I need to select those same cells and format then by changing the color to Green If its "Waiting", I would like to change those cells to Yello What I have been doing is sorting the sheet by the first column the manually selecting the rows and columns and formatting those cells with a right click, format cells, pattern then selecting the color based on the word in "A" column If this could happen automatically when ever this word is chnaged that would be great or even if I could just run a macro to run through the sheet and update row by row that would work as well TIA Jeff W. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula for changing cell format based on more than 3 values | Excel Worksheet Functions | |||
Changing contents of cell based on Cell Format | Excel Worksheet Functions | |||
Changing Cell Background Color based on data from another cell | Excel Discussion (Misc queries) | |||
Changing cell text color based on cell number | Excel Programming | |||
Changing cell text color based on cell number | Excel Programming |