If Range of columns appears in Rng, Do Things

Peteor

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

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

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
132
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,211
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,041
Messages
5,526,417
Members
409,701
Latest member
nitmani

This Week's Hot Topics

Top