Cell highlight

G

gav meredith

Hi,

Does anyone know of a way within excel to have a cell change colour (column
D) when it is simply clicked on. I want the user to simply click on a cell
in column D to have it change red and change back if clicked again.

Any suggestions??
 
V

Vasant Nanavati

There is no good way to accomplish this AFAIK. You can do it with a
double-click or a right-click but there is no click event for a worksheet.
 
V

Vasant Nanavati

Put this in the code module of the worksheet:

Private Sub Worksheet_BeforeDoubleClick _
(ByVal Target As Range, Cancel As Boolean)
Cancel = True
If Not Intersect(Target, Range("D1:D20")) _
Is Nothing Then 'use your desired range
With Target.Font
If .Color = vbRed Then .Color = vbBlack Else .Color = vbRed
End With
End If
End Sub

Of course, since the double-click event is intercepted, the user will not be
able to edit the cell with a double-click.
 
G

gav meredith

again, thank you however this isnt exactly what i am after. I would like the
entire (blank) cell to have a red background. If you look at my other post
'alter existing code', you may have a better understanding. I really
appreciate this, thank you!!!!
 
B

Bob Phillips

Hi Gav,

A slight amendment to Vasant's code

Private Sub Worksheet_BeforeDoubleClick _
(ByVal Target As Range, Cancel As Boolean)
Cancel = True
If Not Intersect(Target, Range("D1:D20")) _
Is Nothing Then 'use your desired range
With Target.Interior
If .ColorIndex = 3 Then
.ColorIndex = xlColorIndexNone
Else
.ColorIndex = 3
End If
End With
End If
End Sub

--

HTH

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