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.

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" wrote:

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.

On May 10, 4:01*pm, robert morris
wrote:
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" wrote:
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.- Hide quoted text -

- Show quoted text -

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

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
On Error Resume Next
End Sub

"Harry's GMail World" wrote:

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.