If Range of columns appears in Rng, Do Things

Peteor

Board Regular
Joined
Mar 16, 2018
Messages
150
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

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

paldob

New Member
Joined
Apr 23, 2018
Messages
28
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:

Peteor

Board Regular
Joined
Mar 16, 2018
Messages
150
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!!!
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,933
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,109
Messages
5,857,439
Members
431,879
Latest member
KiwDaWabbit

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
Top