Change the background color of a whole table line based on a drop down list selection - conditional formatting

Question

In my table (Range("A3:K9999")) there's one column E (E3:E9999), which contains drop down lists.

My goal is to have the bg color changed in the whole table line where the user selects an item of the drop-down list in column E. In a second Worksheet named "Input" is a table (A4:A7) which contains the selectable items of that list boxes.

This is my code so far:

Sub Auto_Open() 'ConditionalFormatting
Dim rg As Range
Set rg = Worksheets(1).Range("A3:K9999")

'clear any existing conditional formatting
rg.FormatConditions.Delete

'define the formatting conditions
Dim cond1, cond2, cond3, cond4 As FormatCondition
Set cond1 = rg.FormatConditions.Add(xlExpression, Formula1:="=$E3=Input!A4")
Set cond2 = rg.FormatConditions.Add(xlExpression, Formula1:="=$E3=Input!A5")
Set cond3 = rg.FormatConditions.Add(xlExpression, Formula1:="=$E3=Input!A6")
Set cond4 = rg.FormatConditions.Add(xlExpression, Formula1:="=$E3=Input!A7")

'define the formatting applied for each condition
With cond1
    .Interior.Color = vbYellow
End With

With cond2
    .Interior.Color = vbRed
End With

With cond3
    .Interior.Color = vbGreen
End With

With cond4
    .Interior.Color = vbBlue
End With
End Sub

With this I'm just getting some crazy colors in the table when I open the excel file... mainly yellow. Even though there are no selections made in the list boxes. How can I make this code work properly in every drop down list of the whole column E (E3:E9999), is that Sub "Auto_Open()" the right location? And is there a reference error in the conditions formula?

Answer 1

VBA Conditional Formatting With Data Validation

Sub AddConditionalFormatting()

    Const sName As String = "Input"
    Const sAddress As String = "A4:A7"
    
    Const dAddress As String = "A3:K9999"
    Const dColumn As Long = 5
    Dim dColors As Variant
    dColors = VBA.Array(vbYellow, vbRed, vbGreen, vbBlue)
    
    Dim sws As Worksheet: Set sws = ThisWorkbook.Worksheets("Input")
    Dim srg As Range: Set srg = sws.Range(sAddress)
    
    Dim dws As Worksheet: Set dws = ThisWorkbook.Worksheets(1) ' improve!
    Dim drg As Range: Set drg = dws.Range(dAddress)
    drg.FormatConditions.Delete
     
    Dim sCell As Range
    Dim r As Long
    
    For Each sCell In srg.Cells
        r = r + 1
        drg.FormatConditions.Add Type:=xlExpression, _
            Formula1:="=" & drg.Cells(dColumn).Address(0) & "='" & sName _
            & "'!" & srg.Cells(r).Address
        drg.FormatConditions(drg.FormatConditions.Count) _
            .Interior.Color = dColors(r - 1)
    Next sCell

    MsgBox "Conditional formatting added.", vbInformation

End Sub