json
06-06-2014, 07:32 AM
Hi. I'm new to VBA Code so thank you in advance for your help.
I am trying to highlight an entire row in excel if two conditions are met. I found this code that answered the question if one condition was met. But How would I add an additional criteria if I need values in column F to also be evaluated?
So for instance column G would need to equal "Break Out" AND Column F would need to be like "Emergency" or "Urgent".
Additionally as exampled above the values in column F (to be considered for highlighting entire row) should be "in ("Emergency", "Urgent")". I know VBA doesn't accept the IN statment, so how would I work a Case into the below code?
credit to mdmackillop for the below code:
Sub Macro1()
Const TEST_COLUMN As String = "D" '<=== change to suit
Dim LastRow As Long
Dim cell As Range
sSheetName = ActiveSheet.Name
With Worksheets(sSheetName)
LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For Each cell In Range("G2:G" & LastRow)
If cell.Value = "Break Down" Then
cell.Offset(, -6).Resize(, 21).Interior.ColorIndex = 39
ElseIf cell.Value = "PM/SM Call" Then
cell.Offset(, -6).Resize(, 21).Interior.ColorIndex = 43
Else
cell.EntireRow.Interior.ColorIndex = xlNone
End If
Next
End With
End Sub
I am trying to highlight an entire row in excel if two conditions are met. I found this code that answered the question if one condition was met. But How would I add an additional criteria if I need values in column F to also be evaluated?
So for instance column G would need to equal "Break Out" AND Column F would need to be like "Emergency" or "Urgent".
Additionally as exampled above the values in column F (to be considered for highlighting entire row) should be "in ("Emergency", "Urgent")". I know VBA doesn't accept the IN statment, so how would I work a Case into the below code?
credit to mdmackillop for the below code:
Sub Macro1()
Const TEST_COLUMN As String = "D" '<=== change to suit
Dim LastRow As Long
Dim cell As Range
sSheetName = ActiveSheet.Name
With Worksheets(sSheetName)
LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For Each cell In Range("G2:G" & LastRow)
If cell.Value = "Break Down" Then
cell.Offset(, -6).Resize(, 21).Interior.ColorIndex = 39
ElseIf cell.Value = "PM/SM Call" Then
cell.Offset(, -6).Resize(, 21).Interior.ColorIndex = 43
Else
cell.EntireRow.Interior.ColorIndex = xlNone
End If
Next
End With
End Sub