change cell format based on another cell's value

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bob - 19 Jul 2007 07:58 GMT

Hi,

I am trying to change the background colour of a cell based on another
cells value.

For instance,
Column E allows numbers 1,2,3,4,5 or 6
Column F has a nested IF formula
=IF((E276=1),"Opportunity",IF((E276=2),"WIP",IF((E276=3),"Submitted",IF((E276=4),"Approved",IF((E276=5),"Committed",IF((E276=6),"Settled",""))))))

If a cell in E column has the value "6" I want the adjacent cell in
column F ( or the whole row) to change colour to Green.

I managed to do this with a Select Case in vba before I created the
Nested IF but now the cell doesn't actually contain the word "Settled",
it contains a formula.

Any help greatly appreciated

Bob

Reply to this Message

Rick Rothstein (MVP - VB) - 19 Jul 2007 10:14 GMT

> I am trying to change the background colour of a cell based on another
> cells value.
>
> If a cell in E column has the value "6" I want the adjacent cell in column
> F ( or the whole row) to change colour to Green.

Give Conditional Formatting a try instead...

Select ALL the rows (not just the E column) from the first row that can have
data to the last row anticipated to hold data, click on Format/Conditional
Formatting in Excel's menu, select "Formula Is" in the first ComboBox and
paste this formula into the second field (it will appear after you select
"Formula Is")...

=$E2=6

(here I assumed the first row of data would be Row 2; change the 2 in the
formula to the actual first row of data). Next, click the Format button,
click the Patterns tab and choose the color you want to use to highlight the
row. Finally, OK your way out of the dialog box.

Whenever the number 6 is entered into Column E of one of the rows you
conditionally formatted, the entire row will highlight with the color you
selected above.

Rick

Reply to this Message