If Range of columns appears in Rng, Do Things

Peteor

Board Regular
Joined
Mar 16, 2018
Messages
152
I have the following:

Sub Automatic_Highlights(Rng As Range)

If Rng.Range("O:T") = True Then GoTo Skip ' In my head this should read "If Rng falls within Range("O:T"), Then GoTo Skip"; Or that's how I want it to read at least.

Rng.Interior.ColorIndex = 7
Intersect(Rng.EntireRow, Range("U:U")).Interior.ColorIndex = 7 ' Is it possible to accomplish the above comment here, making the loop unnecessary?

Skip:
End Sub

Basically I want to exclude columns O:T from the loop, and I can't seem to figure it out. Anyone have any input?
 
Last edited:

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Code:
[LEFT][COLOR=#333333][FONT=Verdana]Sub Automatic_Highlights(Rng As Range)[/FONT][/COLOR][/LEFT]

Dim Cel As Range

For Each Cel in Rng

If Cel.Column > 14 And [COLOR=#222222][FONT=Verdana]Cel[/FONT][/COLOR].Column < 21 Then 
   'Do nothing here
Else
   [LEFT][COLOR=#333333][FONT=Verdana]Cel.Interior.ColorIndex = 7[/FONT][/COLOR][/LEFT]
End If

Next

End Sub

However, this will end the sub if you were to go higher than "T" too
 
Last edited:
Upvote 0
Sub Automatic_Highlights(Rng As Range)


If Rng.Column > 14 Then GoTo Skip
Rng.Interior.ColorIndex = 7
Intersect(Rng.EntireRow, Range("U:U")).Interior.ColorIndex = 7
Skip:
End Sub


This worked, and you gave me the idea. Thank you!!!
 
Upvote 0
Code:
Sub Test()
Dim Rng As Range, Test As Range
Set Rng = Range("O1:T100")  'Change range to suit
Set Test = Intersect(Rng, Range("O:T"))
If Not Test Is Nothing Then
    If Rng.Count = Intersect(Rng, Range("O:T")).Count Then
        MsgBox "All In"
    Else
        MsgBox "Not All IN"
    End If
Else
    MsgBox "None In"
End If
End Sub
 
Upvote 0

Forum statistics

Threads
1,214,746
Messages
6,121,292
Members
449,021
Latest member
bob_messenger

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top