SelectionChange Question



Below is code I am using in a worksheet where the following are headers:

Columns: A1:H1
Rows: A2:A100

Right now, the entire row 1 and the entire column A are filled with the
color I have selected in the script which brings all into the print area.

Can you tell me how to specify the ranges to just the headers, instead of
the entire columns please?

Thanks in advance,



Private Sub worksheet_selectionchange(ByVal target As Excel.Range)

If Cells(1, 1) = "OFF" Then
Exit Sub
End If

C = ActiveCell.Column
R = ActiveCell.Row

Dim Rng1 As Range
Set Rng1 = Range(Cells(1, C), Cells(1, C))

Dim Rng2 As Range
Set Rng2 = Range(Rows(1), Rows(1))

Dim Rng3 As Range
Set Rng3 = Range(Columns(1), Columns(1))

Dim Rng4 As Range
Set Rng4 = Cells(R, 1)

With Rng2
.Interior.Color = RGB(192, 192, 192)
End With

With Rng3
.Interior.Color = RGB(192, 192, 192)
End With

With Rng1
.Interior.Color = RGB(255, 255, 0)
End With

With Rng4
.Interior.Color = RGB(255, 255, 0)
End With

End Sub



Bernie Deitrick


Assuming that your header and column labels are entirely filled:

Dim Rng2 As Range
Set Rng2 = Range(Cells(1, 2), Cells(1, 2).End(xlToRight))

Dim Rng3 As Range
Set Rng3 = Range(Cells(2, 1), Cells(2, 1).End(xlDown))

MS Excel MVP


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question