Combining Two Ranges together.

[omairhe]

Hey excel Gurus,

How do you combine 2 different ranges together with a With statement.

        With Range("A2:A1000" & "U1:FO1")

is the above correct?
thank you.

 

[paldob]

With Range("A2:A1000, U1:FO1")

Would that not work?
​

 

[omairhe]

Function InRange(Range1 As Range, Range2 As Range) As Boolean
' returns True if Range1 is within Range2
Dim InterSectRange As Range
    Set InterSectRange = Application.Intersect(Range1, Range2)
    InRange = Not InterSectRange Is Nothing
    Set InterSectRange = Nothing
End Function




Sub ColourChange2()
  If InRange(ActiveCell, Range("U2:FO1000")) Then
    With Range("A2:A1000")
        .Font.ColorIndex = 1
    End With
    With Range("U1:FO1")
        .Font.ColorIndex = 1
    End With
            Range("A" & ActiveCell.Row).Font.ColorIndex = 4
            With Cells(1, Selection.Column)
                .Font.ColorIndex = 4
            End With
  Else
    With Range("A2:A1000")
        .Font.ColorIndex = 1
    End With
    With Range("U1:FO1")
        .Font.ColorIndex = 1
    End With
  End If
End Sub

I tried to do that with the above code by was not successful..

 

[r1998]

Union(range1,range2)

 

[r1998]

Sub a()
Dim r1 As Range, r2 As Range, r3 As Range
Set r1 = ActiveSheet.Range("a1:a10")
Set r2 = ActiveSheet.Range("b1:b10")
Set r3 = Union(r1, r2)
r3.Select
End Sub

 

[Fluff]

   With Range("A2:A1000,U1:FO1")
      .Font.ColorIndex = 1
   End With

works for me.