[SOLVED] How do I highlight the active cell in a spreadsheet?

[CW]

I want the active cell to change color.

[Peter Rooney]

Hi, CW,

Try pasting this into the code window of the worksheet for which you want to
highlight the active cell.
Change the "22" value in the sixth line to change the colour of the highlight.
Hope this helps

Pete

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells.FormatConditions.Delete
With Target
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
.FormatConditions(1).Interior.ColorIndex = 22
End With
End Sub


"CW" wrote:

> I want the active cell to change color.

[broro183]

Hi,

The below is also pasted into the worksheet's code, but this allow for any conditional formatting etc that already exists on the sheet to be returned after the active cell is changed rather than deleted as Pete's option does with "Cells.FormatConditions.Delete".

Check out the link below for further info/instructions.

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
'sourced from http://www.mcgimpsey.com/excel/highl...iteRetainColor
Const cnNUMCOLS As Long = 10 'if this is 256 it is all columns but macro is slower
Const cnHIGHLIGHTCOLOR As Long = 36 'default lt. yellow
Static rOld As Range
Static nColorIndices(1 To cnNUMCOLS) As Long
Dim i As Long
If Not rOld Is Nothing Then 'Restore color indices
With rOld.Cells
If .Row = ActiveCell.Row Then Exit Sub 'same row, don't restore
For i = 1 To cnNUMCOLS
.Item(i).Interior.ColorIndex = nColorIndices(i)
Next i
End With
End If
Set rOld = Cells(ActiveCell.Row, 1).Resize(1, cnNUMCOLS)
With rOld
For i = 1 To cnNUMCOLS
nColorIndices(i) = .Item(i).Interior.ColorIndex
Next i
.Interior.ColorIndex = cnHIGHLIGHTCOLOR
End With
End Sub

Hth,
Rob Brockett
NZ
Always learning & the best way to learn is to experience...