Conditional Formatting

[BobB]

I’m creating a spreadsheet that compares different data. When in information
in cell B1 is different than A1, I want the cell pattern to be a different
color, but only if there is information in cell B1. Under conditional
formatting I have tried entering condition 1 as “formula =Blank($B$1)†then
set format to no color. Then condition 2 is “cell value is not equal to
$A$1â€, with the formatting equal to pattern light yellow. The manual says it
should apply the first true condition, which if cell B1 is empty should leave
it with no color and only change the format if the information in B1 is
different than A1, but it doesn’t work. Any thoughts?

 

[Roger Govier]

Hi Bob

Try a single condition
=AND(B1<>"",B1<>A1)

make the values absolute with $ signs, if that is what you want.

 

[David Biddulph]

By saying "it doesn't work", you haven't given us much clue as to what
unexpected result you are getting for what inputs.

Firstly I would recommend that you go back into CF and check your conditions
to make sure that you've actually got the conditions you wanted, and that
Excel hasn't turned what you hoped was a condition into merely a text
string. Your condition 1 doesn't sound like a valid condition as there
isn't a function BLANK(). Did you mean ISBLANK()?

You could, of course combine the conditions and have:
formula is =AND($B$1<>$A$1,$B$1<>"")

Finally, if you are applying the condition only to the one cell, B1, you
don't *need* the references to be absolute (with the $ signs), and if you
wish to apply it to a range of cells you are likely to need relative
references.