I don't know if this is even possible

[noexcelguru]

I'm trying to get a CheckBox to find cells with certain values (variable1-4 in the code) and highlight the first cell of the row or the entire row ("A:Q") in orange (CI 45) and remove the color once unchecked.

Is it possible to range the entire column ("E:E"), and not just a specified number of rows down, like ("E7:E5000") in the code?

I've made this code, obviousley it doesn't work, but it might help you to get a clearer picture of what i would like the CheckBox to do

Private Sub CheckBox1_Click()
If CheckBox1.Value = False Then
    If ("E7:E5000") = "Variable1" Or "Variable2" Or "Variable3" Or "Variable4" Then
    firscellofrow = Cells.Interior.ColorIndex = xlNone
    End If
End If
If CheckBox1.Value = True Then
    If ("E7:E5000") = "Variable1" Or "Variable2" Or "Variable3" Or "Variable4" Then
    firscellsofrow = Cells.Interior.ColorIndex = 45
    End If
End If
End Sub

 

[Andrew Poulsom]

Try:

Private Sub CheckBox1_Click()
    Dim r As Variant
    Dim Found As Boolean
    r = Application.Match("Variable1", Columns("E"), False)
    If Not IsError(r) Then
        Found = True
    Else
        r = Application.Match("Variable2", Columns("E"), False)
        If Not IsError(r) Then
            Found = True
        Else
            r = Application.Match("Variable3", Columns("E"), False)
            If Not IsError(r) Then
                Found = True
            Else
                r = Application.Match("Variable4", Columns("E"), False)
                If Not IsError(r) Then
                    Found = True
                End If
            End If
        End If
    End If
    If Found = True Then
        If CheckBox1.Value = False Then
            Rows(r).Interior.ColorIndex = xlNone
        Else
            Rows(r).Interior.ColorIndex = 45
        End If
    End If
End Sub

 

[noexcelguru]

Thanks for the reply, Andrew.

The code you provided me with works, however it only highlights one row and in "prioritized" order from top down. I guess its because of the Else-function?

Come to think of it, i might not have specified this in my post, but i would like all rows containing either text variable1, variable2, variable3 or variable4 in column E to be colored orange.

 

[Andrew Poulsom]

Why can't you use conditional formatting?

 

[noexcelguru]

Yes, that's probably the easisest solution. Do you know if i could turn on/ off conditional formating with the CheckBox, since the idea is to select wether or not you would like those rows highlighted?