Changing cell colour when cell selected/de-selected by mouse/arrow keys

[Joe Hannett]

Newbie. Is it possible to get excel to change the colour
of a cell when it is selected by mouse pointer, or arrow
keys, from default white to yellow then revert to white
again when the next cell is selected?

 

[Bob Phillips]

Here's one way

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells.FormatConditions.Delete
With Target
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
With .FormatConditions(1)
With .Borders(xlTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
With .Borders(xlBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
End With
.FormatConditions(1).Interior.ColorIndex = 6
End With

End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Frank Kabel]

Hi

see:
http://www.cpearson.com/excel/RowLiner.htm

But this kind of event procedure will disbable the UNDO functionality

 

[Guest]

You could use an event macro


Dim OLDCOLOR As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not (OLDCOLOR Is Nothing) Then OLDCOLOR.Interior.ColorIndex = 0
Target.Interior.ColorIndex = 6
Set OLDCOLOR = Target

End Sub

 

[Joe Hannett]

Mr Bob Phillips,

Sir you are a gentleman and a scholar. It worked like a charm. Thank
you from my - nearsighted - manager and I! Your help is truly
appreciated.

Joe Hannett.

 

[Joe Hannett]

Thank you for this info Mr Kabel.

As my manager (for whom I have been trying to figure this out for
months) was unaware of even the UNDO facility (!) I reckon this won't
be a problem.

Kind regards
Joe Hannett