PDA

View Full Version : VBA Nest If Statement help



fijistrom
11-01-2018, 01:59 PM
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
11-01-2018, 03:15 PM
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