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??

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.

--

Vasant


"gav meredith" wrote in message
...
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??

not a problem, how would i do it with a double click??

"Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message
...
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.

--

Vasant


"gav meredith" wrote in message
...
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??

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.

--

Vasant



"gav meredith" wrote in message
...
not a problem, how would i do it with a double click??

"Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message
...
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.

--

Vasant


"gav meredith" wrote in message
...
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??

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!!!!

"Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message
...
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.

--

Vasant



"gav meredith" wrote in message
...
not a problem, how would i do it with a double click??

"Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message
...
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.

--

Vasant


"gav meredith" wrote in message
...
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??

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)

"gav meredith" wrote in message
...
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!!!!

"Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message
...
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.

--

Vasant



"gav meredith" wrote in message
...
not a problem, how would i do it with a double click??

"Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message
...
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.

--

Vasant


"gav meredith" wrote in message
...
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??