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

 

[Jaye Cavallo]

Hello, I need to make an addition to my sheet and I am wondering if the code above will work with an "or" condition. If asking this question in the thread is illegal, I will start another error.

This code fails when i run it with a Method 'Range' of object_Worksheet' failed.

Dim My_Range As Variant

My_Range = Worksheets("Customer_Interactions").Range("A2:A60")

For I = 2 To UBound(My_Range, 1)

If My_Range(I, 1) = "Electric" Or My_Range(I, 1) = "electric" Then
Range(Cells(I, 2)).Interior.ColorIndex = 22
ElseIf My_Range(I, 1) = "Gas" Or My_Range(I, 1) = "gas" Then
Range(Cells(I, 2)).Interior.ColorIndex = 36
ElseIf My_Range(I, 1) = "Other - Specify in Notes" Then
Range(Cells(I, 2)).Interior.ColorIndex = 15

End If
Next I

 

[offthelip]

if you look at my orgina lpost you can see that the way I addressed the range was slightly different syntax . so you need to change:

Range(Cells(I, 2)).Interior.ColorIndex = 22

to

Range(Cells(I, 2),cells(I,2)).Interior.ColorIndex = 22

and similar the other two lines
The "OR" function will work ok

 

[Jaye Cavallo]

Could this code be adjusted to use a dynamic named range, meaning instead of my_range being equal to Worksheets("Customer_Interactions").Range("A1:A3569"), could it be set to a dynamic range named "Interaction_Utility"?

 

[Jaye Cavallo]

Just for clarification, my code is below. This code runs in the worksheet_change event and does not give me an error. However, it does not include the last row in column A when data is entered and the user hits the enter key. It seems the last row of the dynamic range is not included in the "new" range. I guess this code runs before the dynamic range is updated. Can anyone assist with code that would allow me to accomplish this task while using a dynamic range?

Dim My_Range As Variant

My_Range = Worksheets("Customer_Interactions").Range("Interaction_Utility")

For I = 2 To UBound(My_Range, 1)

If My_Range(I, 1) = "Electric" Or My_Range(I, 1) = "electric" Then
Range(Cells(I, 1), Cells(I, 1)).Interior.ColorIndex = 22

ElseIf My_Range(I, 1) = "Gas" Or My_Range(I, 1) = "gas" Then
Range(Cells(I, 1), Cells(I, 1)).Interior.ColorIndex = 36

ElseIf My_Range(I, 1) = "Other - Specify in Notes" Then
Range(Cells(I, 1), Cells(I, 1)).Interior.ColorIndex = 15

ElseIf My_Range(I, 1) = "" Then
Range(Cells(I, 1), Cells(I, 1)).Interior.ColorIndex = xlNone
End If
Next

 

[offthelip]

why not do it just by detecting where the last row is after the entry like this:

Private Sub Worksheet_Change(ByVal Target As Range)
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Dim My_Range As Variant
My_Range = Range(Cells(1, 1), Cells(lastrow, 1))

For I = 2 To UBound(My_Range, 1)

If My_Range(I, 1) = "Electric" Or My_Range(I, 1) = "electric" Then
Range(Cells(I, 1), Cells(I, 1)).Interior.ColorIndex = 22

ElseIf My_Range(I, 1) = "Gas" Or My_Range(I, 1) = "gas" Then
Range(Cells(I, 1), Cells(I, 1)).Interior.ColorIndex = 36

ElseIf My_Range(I, 1) = "Other - Specify in Notes" Then
Range(Cells(I, 1), Cells(I, 1)).Interior.ColorIndex = 15

ElseIf My_Range(I, 1) = "" Then
Range(Cells(I, 1), Cells(I, 1)).Interior.ColorIndex = xlNone
End If
Next
End Sub

 

[Jaye Cavallo]

Thanks again. Would this method/code work if the range is multidimensional, for example "A1:Z80" range?

 

[offthelip]

You can detect the last column if the columns have headers using this code:

LastCol = Cells(1,Columns.Count).End(xlToLeft).Column

There are a number of other ways fo detecting the last row and the last column in VBA, using currentregion or usedrange it really depends on what your worksheet looks like and what might change .

 

[Jaye Cavallo]

I attempted to implement this code, but it causes a compile error, expected an array.

Dim finalRow As Long, finalColumn As Variant
finalRow = _
Worksheets("Communications").Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
finalColumn = _
Worksheets("Communications").Cells.Find("*", searchorder:=xlByColumns, searchdirection:=xlPrevious).Column

Dim Type_Range As Range
Set Type_Range = Worksheets("Communications").Range(Cells(1, 1), Cells(finalRow, finalColumn))

For I = 3 To UBound(Type_Range, 1)

If Type_Range(I, 6) = "Door Hanger" And Type_Range(I, 8) = "" Then
Range(Cells(I, 6), Cells(I, 8)).Interior.ColorIndex = 33
Else

End If
Next I

Obviously, I am doing something wrong.

 

[offthelip]

You are getting ranges and variant arrays mixed up, the loop instruction

For I = 3 To UBound(Type_Range, 1)

requires Type_RAnge to be avariant array, but you defined it as a range, this will fix the compile error:

Dim finalRow As Long, finalColumn As Variant
finalRow = _
Worksheets("Communications").Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
finalColumn = _
Worksheets("Communications").Cells.Find("*", searchorder:=xlByColumns, searchdirection:=xlPrevious).Column

Dim Type_Range As Variant
Type_Range = Worksheets("Communications").Range(Cells(1, 1), Cells(finalRow, finalColumn))

For I = 3 To UBound(Type_Range, 1)

If Type_Range(I, 6) = "Door Hanger" And Type_Range(I, 8) = "" Then
Range(Cells(I, 6), Cells(I, 8)).Interior.ColorIndex = 33
Else

End If
Next I

Note using variant arrays instead of ranges can often make your code more that a 1000 times faster , yes I really mean 1000 times faster

 

[Jaye Cavallo]

I will have to look at the code more closely, because when I change the definition of Type_Range to variant, I get a type mismatch error.