Selecting a range 1

[rarogersonkf]

I have the following snippet

With ActiveCell.Offset(R, 0).EntireRow
If .Interior.ColorIndex = 35 Then
.Interior.ColorIndex = 36
Else
.Interior.ColorIndex = 35
End If
End With

The problem is that the entire row is selected (ActiveCell.Offset(R, 0).EntireRow). What I would like to do is only select a range of colums (for example B to D).

Can someone point me in the correct direction.

Thanks in advance,

Rob

 

[ETID]

'This maybe?....

sheets("sheet1").range("b:d").select
With selection
If .Interior.ColorIndex = 35 Then
.Interior.ColorIndex = 36
Else
.Interior.ColorIndex = 35
End If
End With

 

[rarogersonkf]

Actually I'm trying to use the ActiveCell.Offset(R, 0) where R is a variable incremented as I loop through a recordset. This is where my confusion lies.

Thanks,

Rob

 

[Zathras]

Sounds like you want to use Intersect. Something like this:

Sub test()
For r = 2 To 5
  With Intersect(ActiveCell.Offset(r, 0).EntireRow, Range("B:D"))
    If .Interior.ColorIndex = 35 Then
      .Interior.ColorIndex = 36
    Else
      .Interior.ColorIndex = 35
    End If
  End With
Next r
End Sub

 

[rarogersonkf]

Thanks Zathas, it did exactly as I wanted. One question though, I kinda thought the solution would use a Range object (and frankly I'd never seen Intersect). Is there a Range solution as well?

Thanks again,

Rob

Don't sweat over an answer as I'll use the Intersect, I was just curious.

 

[PHV]

Is there a Range solution as well?
But the Intersect returns a Range object ...

 

[pbrodsky]

Not really sure why you need the offset function.
What about something like:

range(cells(activecell.row,"c"),cells(activecells.row,"g"))

 

[Zathras]

Many ways to skin this cat. Here's another:

Sub Test()
  ToggleColor "E", 2, "F", 5, 36, 35
End Sub

Sub ToggleColor(FirstCol As String, FirstRow As Long, _
                LastCol As String, LastRow As Long, _
                ColorA As Long, ColorB As Long)
  With Range(FirstCol & FirstRow & ":" & LastCol & LastRow)
    If .Interior.ColorIndex = ColorA Then
      .Interior.ColorIndex = ColorB
    Else
      .Interior.ColorIndex = ColorA
    End If
  End With
End Sub

This may be more to your taste for using a "Range solution."