VBA conditional formatting - multiple in same range

[cacos]

Hi!

Trying to figure out why this conditional formatting code (Excel 2007) is not working. It's two relative conditions:

- one adds borders on an entire range if the first cell in that row is not empty.

- the other condition colors a cell in just column1 of that range, if that cell is equal to a named range called "SetVal" (I'm using the mouseover technique from Chandoo).

Sub Format_Range()

    With Sheet1.Range("A1:C40")
   
        .FormatConditions.Delete
        .Activate
        .FormatConditions.Add xlExpression, Formula1:="=$A1<>"""""
        .FormatConditions(1).Borders.LineStyle = xlContinuous
        .FormatConditions(1).Borders.ThemeColor = 1
        .FormatConditions(1).Borders.TintAndShade = -0.249946592608417
        .FormatConditions(1).Borders.Weight = xlThin
        .FormatConditions(1).StopIfTrue = False
       
    End With
   
   
    With Sheet1.Range("A1:A40")
   
        .FormatConditions.Add xlExpression, Formula1:="=$A1=SetVal"
        .FormatConditions(1).Interior.Color = 5
        .FormatConditions(1).StopIfTrue = False
       
    End With

End Sub

Any help is appreciated. Thanks!

 

[Hui]

Try changing the code as per:

    With Sheet1.Range("A1:A40")
  
        .FormatConditions.Add xlExpression, Formula1:="=$A1=SetVal"
        .FormatConditions(2).Interior.Color = 5
        .FormatConditions(2).StopIfTrue = False
      
    End With

 

[cacos]

Yes the (2) did it!

Thanks Hui!