Help with onClick event please...

[Tecnik]

Hi there,

I'm trying to create a procedure that fires when a user clicks a cell, it could be any cell, hence the need for some sort of onClick event.

When clicked the cell changes colour say from white to yellow. If the cell is already yellow it changes back to white.

Here's my code so so far, I've been trying to 'put it in the right place' but I'm still having problems.

' toggle cell colour
If ActiveCell.Interior.ColorIndex = 0 Then
ActiveCell.Interior.ColorIndex = 6 ' Yellow
Else
ActiveCell.Interior.ColorIndex = 0 ' No fill
End If

I know I once found a nice example for this but can't think where I put it.
Please can someone help, or point me in the right direction.

Thanks in advance,

Nick

[mdmackillop]

The best you can do is a Double Click event, but this will do one cell at a time. I would assign a shortcut to the sub, eg Ctrl+y.
[vba]
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Yellow
End Sub

Sub Yellow()
With Selection.Interior
If .ColorIndex <> 6 Then
.ColorIndex = 6
Else
.ColorIndex = xlNone
End If
End With
End Sub

[/vba]

[Bob Phillips]

[vba]

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

End Sub
[/vba]

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

[Tecnik]

Thanks for the help, the macro does exactly what I want.

I've assigned a keyboard shortcut which does as it should.
Double clicking the cell however doesn't fire the script. I'm not sure if I've put the macro in the wrong??

Any ideas??

Thanks again for the help, it'll make someone's life a little easier.

Cheers,

Nick

[benny]

Hello Tecnik,

Change o to xlNone
[vba]Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If ActiveCell.Interior.ColorIndex = xlNone Then
ActiveCell.Interior.ColorIndex = 6
Else
ActiveCell.Interior.ColorIndex = xlNone
End If
End Sub[/vba]

[Tecnik]

Hi there,

Thanks to everyone who helped with this one, it's working perfectly!

I'd put the original code in the wrong place, d'oh!

Cheers,

Nick