I have a spreadsheet that keeps track of various product ratings based on empirical test data. I have an IF statement that assigns a text rating based on a numerial value. For instance, if a result from Test A is less than 30%, then the next column shows the rating as "Good".

My question is related to cell formatting. Is it possible to have another IF formula (or macro) that assigns a cell brackground color based on the rating value. For instance, if the rating value is "Good", I want to be able to automatically have the cell background color set to green.

Has anyone done anything like this?

I don't think you can colour cells using functions - I think you have to use VB script to do that.

It's been a while since I played with Excel in anger though...

You could use Conditional Formatting if you don't need to highlight that many different things.

Here's something that we did for a manager recently.

Sub ChangeTheColor()
Dim CRed, CGreen, CYellow, CNone
CRed = 3
CYellow = 6
CGreen = 4
For Each cell In Selection
'if the cell is less than 33% then color it solid red
If cell.Value < 0.33 And cell.Value >= 0 Then
cell.Interior.ColorIndex = CRed
cell.Interior.Pattern = xlSolid
'if the cell is less than 66% then color it solid yellow
ElseIf cell.Value > 0.33 And cell.Value <= 0.66 Then
cell.Interior.ColorIndex = CYellow
cell.Interior.Pattern = xlSolid
'if the cell is more than 66% then color it solid green
ElseIf cell.Value > 0.66 And cell.Value <= 1 Then
cell.Interior.ColorIndex = CGreen
cell.Interior.Pattern = xlSolid
'anything else remove the color
Else
cell.Interior.ColorIndex = xlNone
End If
Next cell
End Sub


Sorry, it's a little tough to read. The bbs seems to eat the formatting.

Yeah. Look up "conditional formatting" in the Excel help.

Conditional Formatting!
Brilliant!
I take that back. It will only apply 3 conditional formats, and of course I have 4 to apply.

Mach - this macro seems to work by cell increments, what if the cells I want to format are not in a well thought out order? Could the macro search for any cells with the text "Good" or "Poor" and automatically update them based on the text? What if the text was the result of a cell function?

Nevermind. I just found out if I set the default cell format to that of my 4th condition, it will remember it if/when the cell value changes.
Thanks for all the help!