VBA Adding and removing conditional formatting

[TeadyBeeR]

Hello all,

I have an excel file with 3 checkboxes that enable and disable conditional formatting (to find duplicates) using something like this:

Sub ChkBxA()

    Dim rg As Range
    Set rg = Range("F6:H59,J6:CK47,CW6:CW1000")
    Dim Chk As CheckBox


    Set Chk = ActiveSheet.CheckBoxes(Application.Caller)


    If Chk = 1 Then
        Dim uv As UniqueValues
        Set uv = rg.FormatConditions.AddUniqueValues
        uv.DupeUnique = xlDuplicate
        uv.Interior.Color = RGB(205, 250, 200)
        uv.SetFirstPriority
    Else
        rg.FormatConditions(1).Delete
    End If

End Sub

All three are almost identical and only have slightly different ranges (they are partially overlapping) and use different formatting style. Everything works fine as long as I disable those in an opposite order from the order I was turning them on. For example, if I enable them in this order A -> B -> C and disable them in the C -> B -> A order, everything will be fine - highlights will turn on, turn off and there won't be any remaining conditional formatting rules left.

Unfortunately, If I disable them in a different order (for example turn on: A -> B -> C and turn off: A -> B -> C), they won't be disabled properly and I will end up with a broken conditional formatting still working in the file.

Is there a way to make sure that each macro removes conditional formatting that it enabled and not just the last one?

Thanks,
TB

 

[DanteAmor]

The following occurred to me, just change the RGB color to the color number.

Sub ChkBxA()
    Dim rg As Range, Chk As CheckBox, uv As UniqueValues, wcolor As Variant
    
[COLOR=#0000ff]    wcolor = 13171405[/COLOR]
    Set rg = Range("F6:H59,J6:CK47,CW6:CW1000")
    Set Chk = ActiveSheet.CheckBoxes(Application.Caller)
    If Chk = 1 Then
        Set uv = rg.FormatConditions.AddUniqueValues
        uv.DupeUnique = xlDuplicate
        uv.Interior.Color = wcolor ' RGB(205, 250, 200)
        uv.SetFirstPriority
    Else
        For i = 1 To 3
            Set uv = rg.FormatConditions.Item(i)
            If uv.Interior.Color = wcolor Then
                rg.FormatConditions(i).Delete
                Exit For
            End If
        Next
    End If
End Sub


Sub ChkBxB()
    Dim rg As Range, Chk As CheckBox, uv As UniqueValues, wcolor As Variant
    
[COLOR=#0000ff]    wcolor = 13133005[/COLOR]
    Set rg = Range("F6:H59,J6:CK47,CW6:CW1000")
    Set Chk = ActiveSheet.CheckBoxes(Application.Caller)
    If Chk = 1 Then
        Set uv = rg.FormatConditions.AddUniqueValues
        uv.DupeUnique = xlDuplicate
        uv.Interior.Color = wcolor
        uv.SetFirstPriority
    Else
        For i = 1 To 3
            Set uv = rg.FormatConditions.Item(i)
            If uv.Interior.Color = wcolor Then
                rg.FormatConditions(i).Delete
                Exit For
            End If
        Next
    End If
End Sub


Sub ChkBxC()
    Dim rg As Range, Chk As CheckBox, uv As UniqueValues, wcolor As Variant
    
[COLOR=#0000ff]    wcolor = 6579405[/COLOR]
    Set rg = Range("F6:H59,J6:CK47,CW6:CW1000")
    Set Chk = ActiveSheet.CheckBoxes(Application.Caller)
    If Chk = 1 Then
        Set uv = rg.FormatConditions.AddUniqueValues
        uv.DupeUnique = xlDuplicate
        uv.Interior.Color = wcolor
        uv.SetFirstPriority
    Else
        For i = 1 To 3
            Set uv = rg.FormatConditions.Item(i)
            If uv.Interior.Color = wcolor Then
                rg.FormatConditions(i).Delete
                Exit For
            End If
        Next
    End If
End Sub