VBA : IF condition on a conditional formatting interior color does not work

Question

I have the following code, I'm trying to check for duplicates using interior color and conditional formatting. But it seems that it doesn't work. Knowing that I've already set my conditional formatting manually on the worksheet.

Private Sub CommandButton1_Click()

    Dim LR2 As Long

    'A MODIFIER
    With Sheets("A")

        LR2 = .Range("B" & Rows.Count).End(xlUp).Row

        For Each cell2 In .Range("A5:A" & LR2)
            If cell2.Value = "" Then

            cell2.Value = TextBox1.Text
            Exit For

            End If
        Next cell2

        If cell2.Offset(, 1).Text <> 0 Then
        'PROBLEM

            If cell2.Interior.Color <> RGB(255, 199, 206) Then

                Label8.Caption = cell2.Offset(, 1).Text
                Label9.Caption = cell2.Offset(, 2).Text
                Label10.Caption = cell2.Offset(, 3).Text
                Label12.Caption = cell2.Offset(, 4).Text
                Label11.Caption = cell2.Offset(, 5).Text
                Label13.Caption = cell2.Offset(, 6).Text
                CommandButton2.Enabled = True

            Else
                cell2.Value = ""
                MsgBox "Votre bac est déjà saisi", vbExclamation, "Bac double"
                Me.TextBox1.Value = ""

            End If

        Else

        cell2.Value = ""
        MsgBox "Prière de saisir un numéro de bac valide", vbExclamation, "Numéro de bac invalide"
        Me.TextBox1.Value = ""

        End If

    End With
End Sub

Tim Williams · Accepted Answer · 2017-11-19 17:18:43Z

2

You need to use DisplayFormat to pick up CF formats:

If cell2.DisplayFormat.Interior.Color <> RGB(255, 199, 206) Then

answered Nov 19, 2017 at 17:18

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA : IF condition on a conditional formatting interior color does not work

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related