VBA Nest If Statement help

[fijistrom]

I have been looking all through the interwebs looking for help with a nested IF statement. Here is the basic logic of what I am trying to accomplish.

Columns E and F are in a range of E#:F#.

If column e# = yes and column f# = yes then highlight cell gold
ElseIf column e# = yes and column f# = no then highlight cell green
ElseIf column e# = no and column f# = no then highlight row red
Else column e# = no and column f# = yes then highlight row orange


Here is the code I have. It is highlighting the cells with "yes" in gold and the cells with "no" in red.

Sub Macro1()
Dim cells As Range
For Each cells In Range("E2:F94")
If cells.Value = "Yes" And cells.Value ="Yes" Then
cells.Interior.Color = XlRgbColor.rgbGold


ElseIf cells.Value = "Yes" And cells.Value ="No" Then
cells.Interior.Color = XlRgbColor.rgbGreen


ElseIf cells.Value = "No" And cells.Value ="No" Then
cells.Interior.Color = XlRgbColor.rgbOrange


ElseIf cells.Value = "No" And cells.Value ="Yes" Then
cells.Interior.Color = XlRgbColor.rgbGold




End If
Next cells
End Sub


I am looking for some help with trying to get the cells to highlight based on the combination of Yes's and No's.

Thanks,
Nick

[Paul_Hossler]

I didn't understand what

XlRgbColor.rgbGold was supposed to be, so I just used the built in color

palette.

You can change to .Interior.Color = RGB (xx,xx,xx) if you want


Your code was testing each cell in the 2 columns one by one, instead of testing col E and then testing col F

Something like

if E2=Yes and E2=Yes Then

…

If E94=Yes and E94 = Yes Then
…
If F2=Yes and F2=Yes Then
….



This macro just does col E, AND uses .Offset() to get the corresponding col F cell to compare both

Option Explicit

Sub Macro1()
    Dim rCell As Range

    For Each rCell In Range("E2:E94").Rows
        With rCell
            If .Value = "Yes" Then
                If .Offset(0, 1).Value = "Yes" Then
                    .Resize(1, 2).Interior.ColorIndex = 36 '   gold
                ElseIf .Offset(0, 1).Value = "No" Then
                    .Resize(1, 2).Interior.ColorIndex = 43 '   green
                End If
            Else
                If .Offset(0, 1).Value = "Yes" Then
                    .Resize(1, 2).Interior.ColorIndex = 44 '   orange
                ElseIf .Offset(0, 1).Value = "No" Then
                    .Resize(1, 2).Interior.ColorIndex = 3 '   red
                End If
            End If
        End With
    Next
End Sub

Another less straight forward way

Sub Macro2()

    Dim rCell As Range

    For Each rCell In Range("E2:E94").Rows
        With rCell
            Select Case .Value & .Offset(0, 1).Value
                Case "YesYes"
                    .Resize(1, 2).Interior.ColorIndex = 36 '   gold
                Case "YesNo"
                    .Resize(1, 2).Interior.ColorIndex = 43 '   green
                Case "NoYes"
                    .Resize(1, 2).Interior.ColorIndex = 44 '   orange
                Case "NoNo"
                    .Resize(1, 2).Interior.ColorIndex = 3 '   red
            End Select
        End With
    Next

End Sub