Formatting Range

[Daroh]

Hi, I enter data into a worksheet via a userform and want to apply formatting to range (A:J) on sheet1 if column I (countdown in days) is > 5 (Green), <4.9 but > 2 (Orange) and <1.9 Red. I used conditional formatting but every time I delete a row the formatting does not work for the next row. Thanks for your help.

 

[rlv01]

The problem is likely in how you set up your format conditions, but you have not posted any information on those. Properly defined, format conditions will not change if if you delete a row. Here's a VBA example which will set up 3 format conditions that seem to match what you have described above.

Sub SetFormatConditionsColAJ()
    With ActiveSheet.Columns("A:J")
        .FormatConditions.Delete                      'delete old format conditions.

        .FormatConditions.Add Type:=xlExpression, Formula1:="=$I1 < 1.9"
        .FormatConditions(.FormatConditions.count).SetFirstPriority
        .FormatConditions(1).Interior.Color = RGB(255, 0, 0)

        .FormatConditions.Add Type:=xlExpression, Formula1:="=AND($I1 >=1.9,$I1 <= 5)"
        .FormatConditions(.FormatConditions.count).SetFirstPriority
        .FormatConditions(1).Interior.Color = RGB(255, 165, 0)

        .FormatConditions.Add Type:=xlExpression, Formula1:="=$I1 > 5"
        .FormatConditions(.FormatConditions.count).SetFirstPriority
        .FormatConditions(1).Interior.Color = RGB(0, 128, 0)
    End With
End Sub