Row-Entire row changes color (and stays) selecting first entry

[Harry's GMail World]

I have a sheet with 63 rows and 30 columns. I would like the row to
change color and stay as I slide across columns. Freeze panes works
almost but to change color would be better.

 

[robert morris]

Assuming your data starts in A1 - Copy the following VBA - You might want
to try this on a blank Excel Worksheet to see if this is what you want.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Data As Range
Dim i As Integer
Dim j As Integer
Dim k As Integer
i = 1
j = 30
k = ActiveCell.Column()
Set Data = Range("a1:ad60")

Data.Interior.ColorIndex = xlNone

If ActiveCell.Row < 1 Or ActiveCell.Row > 60 Or _
ActiveCell.Column < 1 Or ActiveCell.Column > 30 Then
Exit Sub

End If

ActiveCell.Offset(0, -(k - i)). _
Resize(1, 30).Interior.ColorIndex = 35


End Sub


Bob M

 

[Harry's GMail World]

Assuming your data starts in A1 - Copy the following VBA  - You might want
to try this on a blank Excel Worksheet to see if this is what you want.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Data As Range
Dim i As Integer
Dim j As Integer
Dim k As Integer
i = 1
j = 30
k = ActiveCell.Column()
Set Data = Range("a1:ad60")

Data.Interior.ColorIndex = xlNone

If ActiveCell.Row < 1 Or ActiveCell.Row > 60 Or _
    ActiveCell.Column < 1 Or ActiveCell.Column > 30 Then
    Exit Sub

End If

ActiveCell.Offset(0, -(k - i)). _
Resize(1, 30).Interior.ColorIndex = 35

End Sub

Bob M





- Show quoted text -

OK..I will work on that. I would never have figured that out.
Really..thanks.

 

[Rob]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim Data As Range

'remove previous colour from region
Set Data = Range("a1:ad60")
Data.Interior.ColorIndex = xlNone

On Error GoTo ErrorHandler
Set Target = Intersect(Target, Data) 'errors if target outside
Intersect(Target.EntireRow, Data).Interior.ColorIndex = 35 'errors if no
intersection found
Exit Sub

ErrorHandler: 'if either line errored do nothing