How to change cell colors based on an IF statement formula?

T

Tom Woolman

Hello. I am trying to do a simple color-coded exception report in
Excel and can't find any decent documentation on cell formulas (the
online help in Excel 2000 is kind of arcane).

I am trying to compare a cell value on two different sheets. If there
is a variance >= 1% across the two cells, then I want to say ">= 1%
variance exists, AND I want to change the cell color to red.

So far, here's the formula I am using. I don't think I am doing the
variance calculation correctly, and I can't figure out how to change
the cell color after I change the cell text to ">1% variance exists".

=IF(sheeta!B2-sheetb!B2>=ABS(1%),">1% variance exists","ok")




Any help would be greatly appreciated! Thanks.



Tom
email: [email protected]
 
D

Don Guillett

Conditional formatting does NOT allow references to other worksheets BUT you
can give the other worksheet cell a name like basecell and then use in cell
d14
format>conditionalformatting>left drop select formula is>type in
=d14>basecell*1.01
or whatever.>set formats desired.
 
T

Tom Ogilvy

=IF(Abs(sheeta!B2-sheetb!B2)>=1%,">1% variance exists","ok")

Select the cell and do format=>Conditional Formatting

select cell value is equal to and in the box put ">1% variance exists"
with no double quotes.

Select the format button and under patterns, select red.

OK out.

Regards,
Tom Ogilvy
 
G

Gord Dibben

Tom

Format>Conditional Formatting>Cell Value is:

equal to......... =">1% variance exists"

Note the = sign.

Pick a Format from Patterns.

Gord Dibben XL2002
 
G

Gord Dibben

Tom

Other Tom is correct. You don't have to enter the = sign or the quotes.
Excel will do that for you.



Tom

Format>Conditional Formatting>Cell Value is:

equal to......... =">1% variance exists"

Note the = sign.

Pick a Format from Patterns.

Gord Dibben XL2002
Click to expand...

Gord Dibben XL2002
 
Top