VBA Loop through a Range with Multiple Conditions

[Jaye Cavallo]

Hello,

I am searching for an Excel VBA solution that would accomplish the following:

I have data in 60 rows in columns A through Z, with the value of column P either "Access" "Noise" or "Notification" and the value of column Q as "open" or "closed". How can I write a VBA loop whereby if P="access" and Q="open" then B through Z has an interior color (NOT formatconditions) set to 26? I want to avoid using VBA Conditional Formatting. My data is contained in A2:Z60 (Named range is Data), column P is a Named range of Reason and column Q is status.

I am searching for a solution that is similar to this code used elsewhere in the worksheet, but code with multiple conditions, if true, change the interior color in multiple cells in a given row.

Dim My_Range As Range
Set My_Range = Worksheets("Customer_Interactions").Range("A2:A3569")

For Each cell In My_Range

If cell.Value Like "Electric" Then
cell.Interior.ColorIndex = 22
ElseIf cell.Value Like "Gas" Then
cell.Interior.ColorIndex = 36
ElseIf cell.Value Like "Other - Specify in Notes" Then
cell.Interior.ColorIndex = 15

Else
cell.Interior.ColorIndex = xlNone

End If

Next

 

[offthelip]

try this ( untested). Note I have loaded all your range into a varaint array to make it faster.

Sub test()
Dim My_Range() As Variant
 My_Range = Worksheets("Customer_Interactions").Range("A1:Z60")  ' load values into a varian t array to make it much faster

For i = 1 To UBound(My_Range,1)

If My_Range(i, 16) = "access" And My_Range(i, 17) = "open" Then
Range(Cells(i, 2), Cells(i, 26)).Interior.ColorIndex = 26
Else
Range(Cells(i, 2), Cells(i, 26)).Interior.ColorIndex = xlNone
End If
Next i
End Sub

 

[Jaye Cavallo]

Thank you so much for the suggested code. Unfortunately, it causes a run time error (13) at the line Access_Range = Worksheets("Customer_Interactions").Range("A1:Z60").

 

[Jaye Cavallo]

I defined the variable Access_Range As Variant instead of Access_Range() as Variant and the code runs without error and seems to accomplish the goal. Note: I changed the variable name from My_Range in your code.

Thank you for the solution.

 

[offthelip]

Actually if you run without option explicit you don't need this line at all:

Dim My_Range As Range

I never use option explicit and that it probably why I got the dim line wrong. I wouldn't normally have put it in at all. I find option explicit more trouble that it is worth. QED

 

[Jaye Cavallo]

Actually if you run without option explicit you don't need this line at all:

Dim My_Range As Range

I never use option explicit and that it probably why I got the dim line wrong. I wouldn't normally have put it in at all. I find option explicit more trouble that it is worth. QED

Thanks for the tip. At the risk of a forum no-no, I would like to ask another question. Upon further review, this code changes the color of the header row to nothing. Is there a way for this code to only affect A2:Z60? I tried to change the range of My_Range to A2:Z60, but the code did not give me an error but did not run correctly.

 

[mumps]

Try:

Range(Cells(i + 1, 2)

 

[offthelip]

Try:

Range(Cells(i + 1, 2)

That won't work it will color the wrong row. This is the simple modification:

Sub test()
Dim My_Range() As Variant
 My_Range = Worksheets("Customer_Interactions").Range("A1:Z60")  ' load values into a varian t array to make it much faster

For i = 2 To UBound(My_Range,1)  ' start on the second row!!!

If My_Range(i, 16) = "access" And My_Range(i, 17) = "open" Then
Range(Cells(i, 2), Cells(i, 26)).Interior.ColorIndex = 26
Else
Range(Cells(i, 2), Cells(i, 26)).Interior.ColorIndex = xlNone
End If
Next i
End Sub

 

[mumps]

The OP said that he changed the range to "A2:Z60" which means the the "i'" variable has to be increased by 1.

 

[offthelip]

The OP said that he changed the range to "A2:Z60" which means the the "i'" variable has to be increased by 1.

I agree you could make that change but you need to do it in all 4 places that it is referenced, it is certainly a lot easier to leave the range starting in row 1 and then start the loop in row two. I usually try to keep loop counters the same as row counters unless there is a very good reason not to.