Make a Yes/no cell using a single click

[julene]

I am using Excell 2007.
I am making a spreadsheet that is going to be used by a number of people. I
would like some of the cells to change colour when clicked on, without having
to enter any data, e.g. if the heading is 'native', the user would be able
to click on cells in the column and the cell would change colour to indicate
a 'yes' answer. If the same cell was then changed later to 'no' it could be
clicked on to return to the original colour.
I can change the colour if I enter a Y using conditional formatting, but
don't know how to change it if the cell is just clicked on.
Can you help please?

 

[Stefi]

Try this event sub:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Interior.ColorIndex = 3 Then
Target.Interior.ColorIndex = 0
Else
Target.Interior.ColorIndex = 3
End If
End Sub

Post if you need help to install it!

Regards,
Stefi


„julene†ezt írta:

 

[julene]

Thanks Stefi. I need help.
Thanks again
Julie

Try this event sub:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Interior.ColorIndex = 3 Then
Target.Interior.ColorIndex = 0
Else
Target.Interior.ColorIndex = 3
End If
End Sub

Post if you need help to install it!

Regards,
Stefi


„julene†ezt írta:

 

[Rick Rothstein]

This shorter routine will do the same thing your code does...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Target.Interior.ColorIndex = 3 - Target.Interior.ColorIndex
End Sub

 

[Stefi]

Rick, it's nice and tricky!

Julene,

Open VBA (Alt+F11)
Right click on your worksheet name in the Project window
Select View code from the local menu
Copy and paste Rick's macro in the code window

Regards,
Stefi


„Rick Rothstein†ezt írta:

 

[Rick Rothstein]

For future reference, you can make a single line toggle between any two
values. Here is the general format of such a statement...

Variable = (Value1 + Value2) - Variable

So, if the values to be toggled between are 3 and 7, then the statement
becomes...

Variable = 10 - Variable

Of course, Variable must be first set to one or the other of Value1.
Assuming it is 3, then 10 - 3 gives 7; and if it is 7, then 10 - 7 is 3.

 

[Stefi]

Thanks, Rick, how simple it is after someone invented it!
Stefi


„Rick Rothstein†ezt írta: