VBA FormatConditions formatting bug maybe?

[dt192]

Hi all,

I have some VBA code that clears conditional formatting in the sheet, then adds three new rules.

I am experiencing a bug where the last added rule "A:K" does not apply the formatting, however if I move that section to the top, it then does apply the formatting, but the other two rules do not. If I manually set the formatting only in Conditional Formatting Rules Manager everything works fine.

Any ideas what could be going on? Also is there a better way to determine if the first row contains a date than =AND(ISNUMBER($A1),$A1>40000)?

Thanks in advance for your help.

Public Function Format_cells(ByVal SheetName As String)
    With Sheets(SheetName).Range("A:K")
        .FormatConditions.Delete
    End With
    With Sheets(SheetName).Range("B:B")
        .FormatConditions.Add xlExpression, Formula1:="=COUNTIF(B:B,B1)>1"
        .FormatConditions(1).Interior.Color = RGB(255, 199, 206)
        .FormatConditions(1).Font.Color = RGB(156, 0, 6)
    End With
    With Sheets(SheetName).Range("H:H")
        .FormatConditions.Add xlExpression, Formula1:="=VALUE(H1)>1"
        .FormatConditions(1).Interior.Color = RGB(255, 199, 206)
        .FormatConditions(1).Font.Color = RGB(156, 0, 6)
    End With
        With Sheets(SheetName).Range("A:K")
        .FormatConditions.Add xlExpression, Formula1:="=AND(ISNUMBER($A1),$A1>40000)"
        .FormatConditions(1).Interior.Color = RGB(255, 199, 206)
        .FormatConditions(1).Font.Color = RGB(156, 0, 6)
    End With
End Function

 

[RoryA]

Rewrite the function to use the formatcondition returned by the Add method:

Public Function Format_cells(ByVal SheetName As String)
    With Sheets(SheetName)
        .Range("A:K").FormatConditions.Delete
        With .Range("B:B")
            With .FormatConditions.Add(xlExpression, Formula1:="=COUNTIF(B:B,B1)>1")
                .Interior.Color = RGB(255, 199, 206)
                .Font.Color = RGB(156, 0, 6)
            End With
        End With
        With .Range("H:H")
            With .FormatConditions.Add(xlExpression, Formula1:="=VALUE(H1)>1")
                .Interior.Color = RGB(255, 199, 206)
                .Font.Color = RGB(156, 0, 6)
            End With
        End With
        With .Range("A:K")
            With .FormatConditions.Add(xlExpression, Formula1:="=AND(ISNUMBER($A1),$A1>40000)")
                .Interior.Color = RGB(255, 199, 206)
                .Font.Color = RGB(156, 0, 6)
            End With
        End With
    End With
End Function

 

[dt192]

Thanks Rory, that works perfectly.

 

[RoryA]

Glad to help.