Event Macro - On Cell Activate - Change Cell Format

[Bob]

I'm looking for some code to make any cell change to toggle from
normal background / black background when that cell is clicked.

The idea is to hide / reveal the text in a cell with each click.

Thank you for any ideas.

 

[Guest]

This goes in the worksheet code area:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Interior.ColorIndex = 56 Then
Target.Interior.ColorIndex = xlNone
Exit Sub
End If
If Target.Interior.ColorIndex = xlNone Then
Target.Interior.ColorIndex = 56
End If
End Sub

 

[Rick Rothstein \(MVP - VB\)]

I'm looking for some code to make any cell change to toggle from

normal background / black background when that cell is clicked.

The idea is to hide / reveal the text in a cell with each click.

Gary''s Student gave you a method using the SelectionChange event; but,
personally, I think that interface is somewhat awkward (you have to click
away from a cell in order to click back in it to reverse the change; but
then the color of the cell you clicked away to was changed). If you don't
mind changing the activation keystroke, I think this works a little better.
It involves using the right-click instead of the left click and you can
click within a selected cell to change it back again (assuming it was
clicked by mistake). See if Gary''s Student's, placed in the
BeforeRightClick event code (with an extra statement to cancel the popup
menu) works for you....

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, _
Cancel As Boolean)
Cancel = True
If Target.Interior.ColorIndex = 56 Then
Target.Interior.ColorIndex = xlNone
Exit Sub
End If
If Target.Interior.ColorIndex = xlNone Then
Target.Interior.ColorIndex = 56
End If
End Sub

Remember... right clicking toggles the color.

Rick

 

[Bob]

Gary''s Student gave you a method using the SelectionChange event; but,
personally, I think that interface is somewhat awkward (you have to click
away from a cell in order to click back in it to reverse the change; but
then the color of the cell you clicked away to was changed). If you don't
mind changing the activation keystroke, I think this works a little better.
It involves using the right-click instead of the left click and you can
click within a selected cell to change it back again (assuming it was
clicked by mistake). See if Gary''s Student's, placed in the
BeforeRightClick event code (with an extra statement to cancel the popup
menu) works for you....

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, _
Cancel As Boolean)
Cancel = True
If Target.Interior.ColorIndex = 56 Then
Target.Interior.ColorIndex = xlNone
Exit Sub
End If
If Target.Interior.ColorIndex = xlNone Then
Target.Interior.ColorIndex = 56
End If
End Sub

Remember... right clicking toggles the color.

Rick

Thanks Rick and Gary.

I loaded Rick's code but it doesn't seem to work when I right click on
a cell.

Any ideas on what I've done incorrectly?

Thank you.

 

[Mike Fogleman]

Right-click on the worksheet tab and select View Code from the pop-up menu.
Is the code there? If not, put it there.

Mike F

 

[Don Guillett]

Slightly more efficient.

Cancel = True
With Target
If .Interior.ColorIndex = 56 Then
.Interior.ColorIndex = xlNone
Else
.Interior.ColorIndex = 56
End If
End With

 

[Bob]

Thanks Mike and Don - That works great!

 

[Dana DeLouis]

Maybe another idea...

With Target.Interior
If .ColorIndex = 56 Then
.ColorIndex = xlNone
Else
.ColorIndex = 56
End If
End With