Highlight active cell and de-highlight previous cell

[Guest]

I want to highlight the current cell with a color say gray and set the color
of the last cell I was at back to what it currently was. The code below does
not work. It sets the previous cell color to white and if the cell color was
something other than white, say green, I lose the color green for the
previous cell.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next

Static OldRange As Range

'Set backcolor to whatever, change as needed
Target.Interior.ColorIndex = 15 'light gray

OldRange.Interior.ColorIndex = xlColorIndexNone
Set OldRange = Target

End Sub

 

[Guest]

The problem is that you've got to store the previous ColorIndex. This
creates a new problem if you change the color of the cell you're on. Try the
following, based on your code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
Static OldRange As Range
Static OldIndex As Integer
Const mColor = 15
If OldRange.Interior.ColorIndex = mColor Then
OldRange.Interior.ColorIndex = OldIndex
End If
OldIndex = Target.Interior.ColorIndex
Set OldRange = Target
Target.Interior.ColorIndex = mColor
End Sub

 

[Guest]

Thank you, it worked great.
--
Dave B.

The problem is that you've got to store the previous ColorIndex. This
creates a new problem if you change the color of the cell you're on. Try the
following, based on your code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
Static OldRange As Range
Static OldIndex As Integer
Const mColor = 15
If OldRange.Interior.ColorIndex = mColor Then
OldRange.Interior.ColorIndex = OldIndex
End If
OldIndex = Target.Interior.ColorIndex
Set OldRange = Target
Target.Interior.ColorIndex = mColor
End Sub

 

[Don Guillett]

'McCurdy.Here is something inspired by Don Guillett.

Private Sub Worksheet_selectionChange(ByVal Target As Range)
Dim MyRng As Range
Set MyRng = Target '.EntireRow
Application.EnableEvents = False
On Error GoTo end1
Application.Cells.FormatConditions.Delete
With MyRng
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=ROW()=ROW(INDIRECT(CELL(""address"")))"
'========
'if you want to do fonts too
' With .FormatConditions(1).Font
' .Bold = True
' .Italic = False
' .ColorIndex = 1
' End With
'==========
..FormatConditions(1).Interior.ColorIndex = 36
End With
end1:
Application.EnableEvents = True
End Sub