Can a formula control a cell color also?

[FYRGUY]

This is just "fluff" for my spreadsheet, however I wondered if Excel can control a cell color in an "=if" or other formula.

Can it change a cell "green" for a "true" referance and "red" for a "false" referance?

If so could someone give me a sample of a formula.

Thank you.

Chris

 

[BenRowe]

Yes, Chris, that would be part of Conditional Formatting -- it's in Format. If you're comfortable using excel you should find it easy to use.

 

[AnotherHiggins]

Chris,

Spreadsheet formulas cannot change formatting. They only return values to the cell in which they reside. So there is no "=if" formula syntax that can change colors.

However, as BenRowe pointed out, you can use Format > Conditional Formatting to change the formats of cells meeting up to three different criteria.

Have a look at Excel's help file, and if you have specific questions post back. Be sure to include an example of your data and what criterias you are searching for.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[FYRGUY]

Thank you very much for both replies!!

I found the "conditional formatting" area, however can't seem to get the idea on what info to enter. I have been looking at the Excel help and just don't get it...

I am wanting the cell to be "green" for any number ">0" and "red" for "0".

Under the conditional format dialog box, I have:
"Cell value is" / "Greater than" / =$N$18 ** Here is my problem, I do not know what to enter in this area, (oh, and what does the "$" mean?, when I selected the cell it automatically put them in) I have tried entering it like: "=$N$18>0" , "=($N$18)>0"

I am having a hard time explaining this one, sorry...

Any help would be appriciated!!

 

[FYRGUY]

OK,
Looks like I got it. Thank you so much for your help!!
I just entered in ".1" for green and "0" for red. No other entries.

If you could tell me what the "$" means that would help me understand these formulas.

Thank you very much
Chris

 

[byundt]

The $ means the column letter or row number is an absolute address. If you want the entire row to adopt a color based on the value in column A, then you might use the "Formula Is" criterion of:
=$A1=0.1
This formula assumes that row 1 is the top row in the selected range of cells.

Because the reference to column A is absolute, the conditional formatting formula in cell C15 will look like:
=$A15=0.1
This is because the reference to row 1 got updated to row 15 when you are formatting cells on row 15. But the reference to column A never changes.

This same distinction between absolute addressing and relative addressing (without the $) also applies in regular worksheet formulas. If you put this formula in cell D3 and copy it down:
=SUM($C$2:C2)
The formula will update the reference to the second C2 with each successive row. But the reference to the first C2 will remain fixed. So the formula in cell D48 would be:
=SUM($C$2:C47)

Brad

 

[FYRGUY]

Thank you Brad,

Your explaination was perfect for me, Thank you!!!!

Chris