VBA Highlight Cells multiple criteria

Question

Im trying to create a code within a spreadsheet that will highlight a separate range of cells (A28:D28) when 2 different conditions on cells E13="Country" and F13="State" are met, F13 has a validation dependent on the value on E13 which also has a data validation. So far I have this code, which I think makes no sense at all.

Set A = Range("E13")
    If Not Intersect(Target, A) Is Nothing Then
    If A = "Country" Then
        For Each A In Range("E13")
        If A.Offset(0, 1) = "State" Then
        A.Interior.ColorIndex = 5
        End If
    End If
End If

This code would be used for multiple combinations across multiple sections to highlight multiple ranges but I think I can manage to copy paste and change ranges, Im sorry to say my VBA skills are not that good. All of this goes after a code that returns MsgBoxs when certain criteria is met on a separate set of ranges.

Thank you!

Blake Turner Blake Turner · Accepted Answer · 2018-08-10T20:43:18

Here is the very basic line of code to get the job done:

Sub temp()

    If Range("E13").Value = "Country" And Range("F13").Value = "State" Then Range("A28:D28").Interior.ColorIndex = 5

End Sub

Here is if you need the code to run with E13 changes:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("E13")) Is Nothing Then
    If Range("E13").Value = "Country" And Range("F13").Value = "State" Then Range("A28:D28").Interior.ColorIndex = 5
    'Copy and past the line above with the other criteria you may need
End If

End Sub

How to clear the whole range of color before running the fill color and check whether E13 OR F13 changes:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("E13:F13")) Is Nothing Then
    Range("A28:D38").Interior.Pattern = xlNone 'replace A28:D38 with your range 
    If Range("E13").Value = "Country" And Range("F13").Value = "State" Then Range("A28:D28").Interior.ColorIndex = 5
    'Copy and past the line above with the other criteria you may need
End If

End Sub

VBA Highlight Cells multiple criteria

1 Answers