[SOLVED] Conditional formating in VBA, same color, multiple strings?

Conditional formating in VBA, same color, multiple strings?

I have this code for conditional formatting, where I'm only changing the string:

Cells.FormatConditions.Delete

With Range("$A$1:$H$17").FormatConditions.Add(Type:=xlTextString, String:="CPA", TextOperator:=xlContains)
    .Interior.Color = RGB(105, 191, 44)
End With
With Range("$A$1:$H$17").FormatConditions.Add(Type:=xlTextString, String:="CPN", TextOperator:=xlContains)
    .Interior.Color = RGB(105, 191, 44)
End With
With Range("$A$1:$H$17").FormatConditions.Add(Type:=xlTextString, String:="CSS", TextOperator:=xlContains)
    .Interior.Color = RGB(105, 191, 44)
End With
With Range("$A$1:$H$17").FormatConditions.Add(Type:=xlTextString, String:="RL", TextOperator:=xlContains)
    .Interior.Color = RGB(105, 191, 44)
End With

Is there any alternative for all these lines, so I can write it in a shorter and more effective way?

This macro colors even the cells which contains "CPAzergfzergfer". how can I write a macro where I only color cells containing the exact string?

Solution

You could use an array to specify the conditions for the conditional formatting, like this:

myArray = Array("CPA", "CPN", "CSS", "RL")
For myLoop = LBound(myArray) to UBound(myArray)
    With Range("$A$1:$H$17").FormatConditions.Add(Type:=xlTextString, String:=myArray(myLoop), TextOperator:=xlEqual)
        .Interior.Color = RGB(105, 191, 44)
    End With
Next

I've also changed the TextOperator so it should only select items that match the text value, rather than select items which contain the text value.