Using VBA for Conditional Formatting in Excel 2003

[wongs18]

Hello everyone !

I'm currently stuck because excel 2003 only has 3 criterias for conditional formatting... and I need about 22?..

What I'm trying to do is I'm trying to set for example Apples to be all in colour red and Bananas to be all in Green and Strawberries to be all in Pink and Oranges to be oranges...

But I can't do that in excel 2003 as mentioned before...

Is there a way I can do this on VBA so I can set 22 criterias equal 22 different colours?

Thanks many!

 

[Jeffrey Lopez]

Sure you can!

Give this guy a go...

Sub TasteTheRainbow()


Dim rngCell As Range
Dim lngLstRow As Long
Dim strFruit(1 To 22) As String
Dim strColor(1 To 22) As Integer


lngLstRow = ActiveSheet.UsedRange.Rows.Count


strFruit(1) = "Apple"
strColor(1) = 3
strFruit(2) = "Banana"
strColor(2) = 6
strFruit(3) = "Plum"
strColor(3) = 7


For Each rngCell In Range("A1:A" & lngLstRow)
    For i = 1 To 22
        If rngCell.Value = strFruit(i) Then
            rngCell.Interior.ColorIndex = strColor(i)
        End If
    Next i
Next
       
End Sub

The only caveat is that you must use the color index for the colors.

So just to explain a little, this is using an array and will loop though each one of the values looking for the right one... you can expand this by changing a few lines...

Dim strFruit(1 To 22) As String
Dim strColor(1 To 22) As Integer

You said 22, so I set it to 22, but you can change this number to whatever you like...

strFruit(1) = "Apple"
strColor(1) = 3

Each Fruit will have a value and color index, this can be expanded as much as you need...

For Each rngCell In Range("A1:A" & lngLstRow)

I was assuming your values were in A:A, but this also can be changed to whatever your ranges needs to be!


Hope this helps, and as always be sure to back up your excel workbook before running this macro!

 

[wongs18]

This is amazing! thank you so much!

 

[Jeffrey Lopez]

Glad it works! If you run into any issues, just let me know!