Highlight Cells

[ranswrt]

I would like to highlight a line of cells when the cell is selected. I tried
the following code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng As Range
Dim ycell As Range

Set ycell = ActiveCell.EntireRow.Cells(3)
Set rng = Range(ycell, ycell.Offset(0, 5))
rng.Interior.ColorIndex = 36

End Sub

but I get an error that says 'Unable to set ColorIndex property of the
interior class'.
What am I doing wrong here? or is there a better way to do this?
Thanks

 

[Jim Thomlinson]

That code works for me on a brand new workbook. Is your worksheet protected???

 

[Rick Rothstein \(MVP - VB\)]

I'm not completely sure what range you are trying to color... is it a 3
columns wide by 6 rows deep with the active cell in the upper left of that
rectangle? If so, is this what you want to do...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
UsedRange.Interior.ColorIndex = xlNone
Target.Resize(6, 3).Interior.ColorIndex = 36
End Sub

Rick

 

[Rick Rothstein \(MVP - VB\)]

Okay, I see what you are trying to highlight now. Try this event code
instead...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
UsedRange.Interior.ColorIndex = xlNone
Cells(Target.Row, 3).Resize(1, 6).Interior.ColorIndex = 36
End Sub

Note that it removes the previous highlight (I tried to guess at your
intent). If you do not want that feature, delete the first line (the
UsedRange one) and leave just the last line.

Rick

 

[ranswrt]

It was protected that was the problem.
Thank you

That code works for me on a brand new workbook. Is your worksheet protected???

 

[ranswrt]

I tried your code but I have a problem with the 'usedrange'. It is changing
the color on a larger range instead of the previous range. I am not familiar

 

[Dave Peterson]

Make it:

Me.UsedRange.Interior.ColorIndex = xlNone

Me is the object that owns the code--in this case, it's the worksheet with the
code.

 

[Jim Thomlinson]

That gets a bit tricky. You need to store the range in a static variable.
Here is some code that I use, which you can probalby adapt without too much
trouble...

*In a standard module****
Option Explicit

'***This code has the side effect that you can not copy on any sheet it is
run against

Public HighlightRow As clsHighlightRows

Public Sub Auto_Open()
Set HighlightRow = New clsHighlightRows

HighlightRow.AddSheet Sheet1
HighlightRow.AddSheet Sheet2

End Sub

Public Sub Auto_Close()
Set HighlightRow = Nothing
End Sub

*in a Class Module********
Option Explicit
Private HighlightSheets As New Collection
Private WithEvents xlApp As Excel.Application
Private rngOldTarget As Range

Private Sub Class_Initialize()
Set xlApp = Excel.Application
End Sub


Private Sub Class_Terminate()
Set xlApp = Nothing
Set HighlightSheets = Nothing
End Sub

Private Sub xlApp_SheetActivate(ByVal Sh As Object)
'Initialize the last cell to the current cell of this sheet
Set rngOldTarget = ActiveCell

'Highlight the Font of the current cell if necessary
Call xlApp_SheetSelectionChange(Sh, rngOldTarget)
End Sub

Private Sub xlApp_SheetSelectionChange(ByVal Sh As Object, ByVal Target As
Range)
Dim wks As Worksheet

On Error Resume Next
Set wks = HighlightSheets.Item(Sh.Name)
On Error GoTo 0

If Not wks Is Nothing Then Call HighlightRow(Sh, Target)
End Sub


Public Function AddSheet(ByVal wks As Worksheet)
Call HighlightSheets.Add(wks, wks.Name)
End Function

Public Function RemoveSheet(ByVal wks As Worksheet)
Call HighlightSheets.Remove(wks.Name)
End Function

Public Property Get Items() As Collection
Set Items = HighlightSheets
End Property

Private Sub HighlightRow(ByVal Sh As Object, ByVal Target As Range)

If Not (rngOldTarget Is Nothing) Then
rngOldTarget.EntireRow.Interior.ColorIndex = xlNone
End If
'Set Last cell = Current cell
Set rngOldTarget = Target
Target.EntireRow.Interior.ColorIndex = 36
End Sub

 

[ranswrt]

Thanks I'll work on those.

I tried your code but I have a problem with the 'usedrange'. It is changing
the color on a larger range instead of the previous range. I am not familiar

 

[shiro]

Mr Jim,
What is the simplest way to higlighted the
edited cell?