Evaluate Multiple Ranges

[DBaker7777]

The code below only looks in the Range ("U41:U1000") however I would also like it to check Range("W41:W1000"), but I can't seem to get it to include that range as well. I have attempted Range("U41:U1000", "W41:W1000"), but that did not provide the results I was looking for. I need it to check both ranges then hide the row if it does not equal U38. It seemed like it was only looking in Range("W41:W1000") each time.

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveWorkbook.ActiveSheet.Unprotect
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
If Target.Row = 38 Then
For Each c In Range("U41:U1000")
If c.Value = Range("U38").Value Then
c.EntireRow.Hidden = False
Else
c.EntireRow.Hidden = True
End If
Next c
End If
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
ActiveWorkbook.ActiveSheet.Protect
End Sub

 

[VoG]

Try

For Each c In Range("U41:U1000,W41:W1000")

 

[DBaker7777]

When I tried that it seems to be only evaluating Range("W41:W1000") as it will not provide me with any results if Range("U41:U1000") = "U38"

 

[VoG]

It should be checking both columns. Try this on a blank sheet - it will colour all the cells in the range yellow:

Sub test()
Dim c As Range
For Each c In Range("U41:U1000,W41:W1000")
    c.Interior.ColorIndex = 6
Next c
End Sub

 

[DBaker7777]

ADVERTISEMENT

It did indeed color both ranges, but could this be my problem:

Private Sub Worksheet_Change(ByVal Target As Range)
end sub

 

[VoG]

This will only work if you enter a value in row 38.

 

[DBaker7777]

This happens and starts the trigger for evaluating which rows should be visible and which ones shouldn't be visible. The value in U38 is "True" so when it sees a true in either U or W it should not hide the row, but it seems to be doing on the the trues in column W and not even evaluating column U. I checked to ensure there were in fact trues in column U and there were. Not sure why it is not working.