Help with Excel 2007 (Conditional Formatting)

[Gollom]

Any experts out there?

I want to change the fill colour (to custom colour RGB 5,255.5) of cell range A1-A10 if the contents of cell A9 is the word 'Green'.

I'm almost there but can't quite get it. I know how to change the cell that the word is in using Conditional Formatting.

The formula would be appreciated

 

[BTB 500]

I don't have Excel 2007, but under conditional formatting you'd normally select "Formula is" as the condition (rather than "Cell Value is") ... then just put in a formula that returns 'true' (value 1) when you want the colour to change.

i.e.

=if(a9="Green",1,0)

 

[imadoofus]

You can't apply conditional formatting to a cell other than the one the formatting in applied to. In other words, you can't use a condition in one cell to affect the formatting in another.

Therefore, as Bill says, you need a formula in each of cells A1 - A10. And that may get in the way of whatever else you have there, and won't work if you want the user to enter data into those cells.

I'd use a section fo the sheet for data entry, and another for data output.

PJ

 

[BTB 500]

You can do it with just a single conditional format action.

Highlight the range of cells you want to go green based on what's in A9.

Select conditional formatting, put in the formula as above but you need $a$9 rather than a9.

Just tried it, and it works fine (in my version of Excel, anyway).

 

[SportsCoupeRich]

I would use an input sheet and an output sheet to be honest - i do a lot of financial modelling and it is the only way to get a really secure model in my opinion.

 

[imadoofus]

Are you sure? I have Excel 2003 on the machine I'm in front of, and you can only apply formatting to a cell based upon what's in that specific cell.

You're probably right, but I thought I knew Excel pretty well, and I've never been able to do that.

 

[Gollom]

I would use an input sheet and an output sheet to be honest - i do a lot of financial modelling and it is the only way to get a really secure model in my opinion.

Thanks to all but not there yet (Excel 2007 seems to allow less granular control over things!)

I can see the logic in 2 sheets but my process does not allow for that

 

[imadoofus]

I've just worked out how to do it :bannana:, and Bill's right! :bannana:

Sorry Bill

 

[Gollom]

I've just worked out how to do it :bannana:, and Bill's right! :bannana:

Sorry Bill

Still confused of Preston here

 

[imadoofus]

See here. That explains it more clearly than I (or Bill ) can.

PJ

 

[BTB 500]

See here. That explains it more clearly than I (or Bill ) can.

PJ

I just finished typing all that, then saw your link when I hit 'Preview' !