Conditional formatting an Excel 2003 colomn

[longbow007]

Hello, could some one please help me with the following:

In column P of my Excel 2003 worksheet named: Data, I need the following text in the cells to have the interior colors (as shown below):

Any Situation - RGB = 255,204,0 (with black text);
Apples - RGB = 153, 204, 0 (with black text);
Apples / Rasberry = 153, 153, 255 (with black text);
Jam = 128,0,128 (with white text);
Nectarine = 0,0,128 (with white text);
Nectarine / Apples = 255,255,153 (with black text);
Nectarine / Rasberry = 255,204,153 (with black text);
Orange = 255,153,204 (with black text);
Rasberry = 153,204,255 (with black text); and
Sausage = 0,0,0 (with white text).

The range is P3:P20

I tried using conditional formatting but it only allowed me to use three conditions.

If any one could help, it would be greatly appreciated.

Kind regards,

Chris

[Evagrius]

Hello Chris,

Please try the below code. I assume the data you provided is sample data. Simply change the strings in the code to fit your needs. You may also add additional "select case" statements as needed following the syntax I have provided.

Sub MyColor()
Dim MyRange As Range
Dim MyC As Range
Set MyRange = Range("P3:P20")

For Each MyC In MyRange
    Select Case MyC.Value
        Case "Any Situation"
            MyC.Interior.Color = RGB(255, 204, 0)
        Case "Apples"
            MyC.Interior.Color = RGB(152, 204, 0)
        Case "Jam"
            MyC.Interior.Color = RGB(128, 0, 128)
            MyC.Font.Color = RGB(255, 255, 255)
        Case "Nectarine"
            MyC.Interior.Color = RGB(0, 0, 128)
            MyC.Font.Color = RGB(255, 255, 255)
        Case Else
            MyC.Interior.ColorIndex = xlNone
    End Select
Next MyC
    
End Sub

[Trebor76]

Hi longbow007,

With that many conditions in 2003, the only solution is VBA.

The following four steps should do the job:

1. Copy the following code to the clipboard

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("P3:P20")) Is Nothing Then
        Select Case Target
            Case "Apples"
                With Target
                    .Interior.Color = RGB(153, 204, 0)
                    .Font.Color = RGB(0, 0, 0)
                End With
            Case "Apples / Rasberry"
                With Target
                    .Interior.Color = RGB(153, 153, 255)
                    .Font.Color = RGB(0, 0, 0)
                End With
            Case "Jam"
                With Target
                    .Interior.Color = RGB(128, 0, 128)
                    .Font.Color = RGB(255, 255, 255)
                End With
            Case "Nectarine"
                With Target
                    .Interior.Color = RGB(0, 0, 128)
                    .Font.Color = RGB(255, 255, 255)
                End With
            Case "Nectarine / Apples"
                With Target
                    .Interior.Color = RGB(255, 255, 153)
                    .Font.Color = RGB(0, 0, 0)
                End With
            Case "Nectarine / Rasberry"
                With Target
                    .Interior.Color = RGB(255, 204, 153)
                    .Font.Color = RGB(0, 0, 0)
                End With
            Case "Orange"
                With Target
                    .Interior.Color = RGB(255, 153, 204)
                    .Font.Color = RGB(0, 0, 0)
                End With
            Case "Rasberry"
                With Target
                    .Interior.Color = RGB(153, 204, 255)
                    .Font.Color = RGB(0, 0, 0)
                End With
            Case "Sausage"
                With Target
                    .Interior.Color = RGB(0, 0, 0)
                    .Font.Color = RGB(255, 255, 255)
                End With
            Case Else
                With Target
                    .Interior.Color = RGB(255, 204, 0)
                    .Font.Color = RGB(0, 0, 0)
                End With
        End Select
        
    End If

End Sub

2. Right click the relevant tab you want the code to run on and from the shortcut menu select View Code

3. Paste the code from step one into the blank VBA module

4. From the File menu select Close and Return to Microsoft Excel

HTH

Robert

[longbow007]

Thank you so much Trebor76 and Evagrius for all your wonderful help and assistance - it is very much appreciated.

Kind regards,

Chris

[Evagrius]

You're very welcome Chris.

[Trebor76]

Yes, you are Chris - and thanks for taking the time to mark the thread as solved