excel: change the color of a cell by clicking on it

[davikokar]

Hallo,

I have two defined colors (red / green) and some cells that are either red or green. I would like that these cells would change their color into red or green when I click on them (into red when I click on a green cell and viceversa). But I don't know which event to use...

thanks for suggestions

 

[AnotherHiggins]

Right click on the sheet tab where you want this to happen, select View Code.

In the window that opens up, place this code:

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

NOTE: This will only work if you use the standard RED and GREEN colors. If you are using SEA GREEN, BRIGHT GREEN or TEAL, you will need to change the color index used.

You can find a desired color index by turning on your macro recorder (Tools > Macro > Record New Macro) and highlighting a cell whatever color you want. Then observe the generated code to learn the color index.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[SkipVought]

Just a very minor suggestion for using "standard" colors. There are a handful of VB color Constants (very limited)...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Interior.Color = vbred Then
    Target.Interior.Color = vbgreen
ElseIf Target.Interior.Color = vbgreen Then
    Target.Interior.Color = vbred
End If
End Sub

Skip,

_[red][/red]