Selecting Two Ranages

[mayoung]

Thank You in adavnce for any help on this.

I am wanting to do a loop based on the criteria in column AN.

If I start my loop in cell AN2 and loop down and it comes to a cell that has the word "CLOSED". Lets use cell AN6 as an example. I then want to select A6:AK6 and AM6:AP6 and make the cells interior color a light red. Or if the cell contains "IN SHOP" to highlight the cells interior a light green. Then move down to AN7 and so on. I have never done this before seleting two ranges at once.

I would think I have to use offset or cells in selecting the ranges?

Any suggerstions on how to go about his. Maybe my approach is wrong and there is better way to achieve this.

Thank You

 

[Trevor G]

You should look to use Conditional Formatting.

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG04Nov33
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, R [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range("AN1", Range("AN" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]Set[/COLOR] R = Union(Cells(Dn.Row, 1).Resize(, 37), Cells(Dn.Row, 39).Resize(, 4))
    [COLOR="Navy"]Select[/COLOR] [COLOR="Navy"]Case[/COLOR] Dn.Value
        [COLOR="Navy"]Case[/COLOR] "CLOSED": R.Interior.ColorIndex = 3
        [COLOR="Navy"]Case[/COLOR] "IN SHOP": R.Interior.ColorIndex = 43
    [COLOR="Navy"]End[/COLOR] Select
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[MARK858]

Just another way as I had already typed it (no better than what MickG has posted), change the colors to suit (and to a certain extent I do agree with Trevor G).

Sub hLight()
    Dim myCell As Range
    Application.ScreenUpdating = False

    For Each myCell In Range("AN2:AN" & Range("AN" & Rows.Count).End(xlUp).Row)
        If myCell.Value = "CLOSED" Then Intersect(myCell.EntireRow, Range("A:AK,AM:AP")).Interior.Color = 4875260
        If myCell.Value = "IN SHOP" Then Intersect(myCell.EntireRow, Range("A:AK,AM:AP")).Interior.Color = 8778658
    Next

    Application.ScreenUpdating = True
End Sub