I need to write a macro that fills in the background color of a column based
on the cell contents. It has five different possible conditions and all 5
need different colors. Excel 2003 Thanks

Got my code!!!
Sub Color()
Set Rank = Range("C2:C20")
For Each Cell In Rank

If Cell.Value = "Needs Improvement" Then
Cell.Interior.ColorIndex = 3
End If
If Cell.Value = "Below Requirements" Then
Cell.Interior.ColorIndex = 45
End If
If Cell.Value = "Meets Requirements" Then
Cell.Interior.ColorIndex = 6

End If
If Cell.Value = "Exceeds" Then
Cell.Interior.ColorIndex = 4
End If

If Cell.Value = "Superstar" Then
Cell.Interior.ColorIndex = 8
End If

If Cell.Value < "Needs Improvement" And Cell.Value < "Below
Requirements" And Cell.Value < "Meets Requirements" And Cell.Value <
"Exceeds" And Cell.Value < "Superstar" Then
Cell.Interior.ColorIndex = xlNone
End If

Next
End Sub

Why don't you use a select-case statement instead of those funky if
statements?

Just type "select" put your cursor in front of select and hit H1 for a
help example.

Here is some worksheet event code.

Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("A1:A100") 'adjust to suit
If Intersect(Target, r) Is Nothing Then
Exit Sub
End If
vals = Array("C", "D", "G", "H", "K") 'values
nums = Array(8, 9, 6, 3, 7) 'colors
For Each RR In r
icolor = 0
For I = LBound(vals) To UBound(vals)
If UCase(RR.Value) = vals(I) Then
icolor = nums(I)
End If
Next
If icolor 0 Then
RR.Interior.ColorIndex = icolor
End If
Next
End Sub

Right-click on the sheet tab and "View Code" Copy/paste the code into that
sheet module.

Edit to suit your needs.


Gord Dibben MS Excel MVP

On Sun, 1 Mar 2009 20:43:01 -0800, Chad Portman
wrote:

I need to write a macro that fills in the background color of a column based
on the cell contents. It has five different possible conditions and all 5
need different colors. Excel 2003 Thanks