Highlight Cells
 

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

Highlight Cells
 

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

Jim Thomlinson


"ranswrt" wrote:

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

Highlight Cells
 

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


"ranswrt" wrote in message
...
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

Highlight Cells
 

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


"Rick Rothstein (MVP - VB)" wrote in
message ...
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


"ranswrt" wrote in message
...
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

Highlight Cells
 

It was protected that was the problem.
Thank you

"Jim Thomlinson" wrote:

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

Jim Thomlinson


"ranswrt" wrote:

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

Highlight Cells
 

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
on how to use that code. How do I change it to just the previous range that
had the color change?


"Rick Rothstein (MVP - VB)" wrote:

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


"Rick Rothstein (MVP - VB)" wrote in
message ...
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


"ranswrt" wrote in message
...
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

Highlight Cells
 

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.

ranswrt wrote:

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
on how to use that code. How do I change it to just the previous range that
had the color change?

"Rick Rothstein (MVP - VB)" wrote:

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


"Rick Rothstein (MVP - VB)" wrote in
message ...
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


"ranswrt" wrote in message
...
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




--

Dave Peterson

Highlight Cells
 

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
--
HTH...

Jim Thomlinson


"ranswrt" wrote:

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
on how to use that code. How do I change it to just the previous range that
had the color change?


"Rick Rothstein (MVP - VB)" wrote:

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


"Rick Rothstein (MVP - VB)" wrote in
message ...
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


"ranswrt" wrote in message
...
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

Highlight Cells
 

Thanks I'll work on those.

"ranswrt" wrote:

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
on how to use that code. How do I change it to just the previous range that
had the color change?


"Rick Rothstein (MVP - VB)" wrote:

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


"Rick Rothstein (MVP - VB)" wrote in
message ...
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


"ranswrt" wrote in message
...
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

Highlight Cells
 

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


"Jim Thomlinson" wrote in message
...
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
--
HTH...

Jim Thomlinson


"ranswrt" wrote:

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
on how to use that code. How do I change it to just the previous range
that
had the color change?


"Rick Rothstein (MVP - VB)" wrote:

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


"Rick Rothstein (MVP - VB)"
wrote in
message ...
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


"ranswrt" wrote in message
...
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