Search for string on cell starting with two specific letters and highlight entire row

0
votes

I want to highlight the row that contains letters starting with "ZZ*" green
Then highlight the row that contains letters starting with "ZV*" yellow
and so on.

I used conditional formatting formula inside VB script, it works with the first condition but then ignores the second condition. If anyone has a different way or can improve my code it'll be great. thank you. 

Sub color1()
   With Range("A:Z").FormatConditions
      .Delete
      Range("A2").Activate
      .Add Type:=xlExpression, Formula1:="=ISNUMBER(MATCH(""ZZ*"",1:1,0))"
      .Item(1).Interior.ColorIndex = 41    
   End With
End Sub

The A column has 

ZZ3543   
ZV5635   
ZX4635   
ZC3456     
ZV3456
2
excelvbscriptvba
FYI - vbscript is a different language from VBA. If you're writing macros in Excel then you're using VBA, so using "vbscript" in your question may cause some confusion... – Tim Williams

2 Answers

0
votes

This one works for me:

Sub color1()
    'change Sheet1 to suit
    With ThisWorkbook.Worksheets("Sheet1").Range("A:Z").FormatConditions
        .Delete
        .Add Type:=xlExpression, Formula1:="=ISNUMBER(MATCH(""ZZ*"",1:1,0))"
        .Item(1).Interior.ColorIndex = 4 'green
        .Add Type:=xlExpression, Formula1:="=ISNUMBER(MATCH(""ZV*"",1:1,0))"
        .Item(2).Interior.ColorIndex = 6 'yellow
    End With
End Sub

enter image description here

0
votes

Here's something you can start with:

Sub Color1()
    For i = 1 To 5
        If Left(Sheet1.Cells(i, 1), 2) = "ZZ" Then Sheet1.Rows(i).Interior.ColorIndex = 41
    Next
End Sub