How can I change a cell's background color based on the cell's current value?

I have a column with function values of True or False:

=IF(ISNA(A1), false, true)

from row 1 to 5000+ rows. I would like to mark all the False as red background color or text as red. Is there any function available?

David.Chu.ca

Posted 2010-03-31T21:22:13.263

Reputation: 2 967

Answers

First, you change your formula to

=ISNA(A1)

you don't need the IF part. Then use conditional formatting. First Select B1:B5000 (or whatever column your formula is in. Next Format - Conditional Formatting. Then choose Cell Values Is and type False into the box. Click the Format button to set the Pattern to red. Click OK.

Worksheet formulas can only return values. They cannot change other properties of the cell (like colors). Conditional Formatting is the way to go here.

dkusleika

Posted 2010-03-31T21:22:13.263

Reputation: 1 776

OK. I see your point. Another option may be to write a snip of VBA macro to loop the range to change cell or row color (text or background). By the way, I prefer True and False values. In the example, A1 actually contains another formula so ISNA() will always returns False. – David.Chu.ca – 2010-03-31T22:11:42.177

I'm not very familiar with Excel but have you tried Conditional Formatting?
You can find it under

Toolbar->Format->Conditional Formatting

^{(Translated. I have a non english version of Office)}

Nifle

Posted 2010-03-31T21:22:13.263

Reputation: 31 337

Is is nice. However, I have to apply the style to 5000+ rows. Not sure if there is function available to do it so that I can copy and paste. – David.Chu.ca – 2010-03-31T21:42:29.730

OK. I can do copy first and then select a range, right click, and finally select Paste Special->Format. – David.Chu.ca – 2010-03-31T21:48:39.467