SelectionChange Question

[porter444]

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,

Scott

/////////////////////////////////////

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]

Porter,

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))

HTH,
Bernie
MS Excel MVP

 

[porter444]

Perfect, thank you!