Change cell back color on click

[MaT]

This must be very obvious to you guys, but can you advise
a newbie to Excel VBA programming (not too bad in Access
VBA) how to do the following:
I want to click highlight a cell, then click on a button
and change the cell back color to say Red ?
Simple... any help please ?
Thanks
Mat

 

[Bob Umlas]

Sub TurnRed()
Selection.Interior.ColorIndex = 3
End Sub
Bob Umlas, MVP
FYI, I'll be leading a LIVE 1-hour FREE webinar on tips & tricks on January
27 from 4-5 PM est. It's done from your computer. To find out more &
register, go to http://www.iil.com, click on the yellowish rectangle on the
left "Try a free webinar", click the link for Microsoft Tips & Tricks. Maybe
I'll "see" you there!

 

[Bob Phillips]

Do it all in one go, select and colour

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

End Sub



'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.


This will reset red cells back to no colour.

 

[Mat]

Hi

I must be thick - but I can't get this to run - I need it
to change color on a click event with a button on the
worksheet. I have tried

Private Sub cmdButton_Click(ByVal Target As Range)
With Target
If .Interior.ColorIndex = 3 Then
.Interior.ColorIndex = xlColorIndexNone
Else
.Interior.ColorIndex = 3
End If
End With
End Sub


However, it does not like this ... "Compile error -
procedure declaration does not match description of event
having the same name"

Any help ?
Thanks
Mat.

 

[Dave Peterson]

Bob's suggestion was to check/change fill colors when you changed selection. It
would be pretty automatic.

But I added a commandbutton from the control toolbox toolbar on a worksheet and
double clicked on it.

Then I used this slightly modified code and it worked ok:

Option Explicit
Private Sub CommandButton1_Click()
With Selection
If .Cells(1).Interior.ColorIndex = 3 Then
.Interior.ColorIndex = xlColorIndexNone
Else
.Interior.ColorIndex = 3
End If
End With
End Sub

(My button was named CommandButton1--not cmdButton.)

And note that the code now works on the selection. It checks the first cell of
the selection and changes color based on what it finds in there.

You could have it check each cell and toggle things accordingly:

Option Explicit
Private Sub CommandButton1_Click()
Dim myCell As Range
For Each myCell In Selection.Cells
With myCell
If .Interior.ColorIndex = 3 Then
.Interior.ColorIndex = xlColorIndexNone
Else
.Interior.ColorIndex = 3
End If
End With
Next myCell
End Sub