Extend the range of selection to a row above and below in vba

Question

I'm trying to select cells right of A3, and select the row above and below, and set the background to black and font to white in VBA. But my code will do something shown in the picture above.

Sub header()
    Range("A2:A4").Select
    Range("A3").Activate
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Interior.ColorIndex = 1
    Selection.Font.ColorIndex = 2
End Sub

That is because `.Activate` does not cancel `.Select`, which means `.End(xlToRight)` is applied to the entire A2:A4. [Why](https://stackoverflow.com/q/10714251/11683) are you trying to select in the first place? — GSerg, Mar 06 '23 at 19:01

score 1 · Accepted Answer · answered Mar 06 '23 at 19:09

No need to Select.

Dim ws As Worksheet
Set ws = ActiveSheet

With ws
    Dim lastCol As Long
    lastCol = .Cells(3, .Columns.Count).End(xlToLeft).Column

    Dim rng As Range
    Set rng = .Range("A2", .Cells(4, lastCol))
End With
    
rng.Interior.ColorIndex = 1
rng.Font.ColorIndex = 2

I'd suggest using .Color instead of .ColorIndex:

rng.Interior.Color = vbBlack
rng.Font.Color = vbWhite

Extend the range of selection to a row above and below in vba

1 Answers1