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
Mat wrote:
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.
-----Original Message-----
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.
--
HTH
Bob Phillips
"Bob Umlas" wrote in message
...
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!
"MaT" wrote in
message
...
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
.
--
Dave Peterson