Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Dave Peterson
 
Posts: n/a
Default Change cell back color on click

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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
In excel, I seem to be "locked" on a cell and unable to click off. Bubba Junie Excel Discussion (Misc queries) 7 May 1st 06 07:46 AM
Color change in cell when > 49.99 Slumbering Gorilla Excel Discussion (Misc queries) 1 January 24th 05 09:11 PM
Keyboard shortcut for double click in Excell cell Katherine Excel Discussion (Misc queries) 10 January 13th 05 01:28 AM
Undoing LINKS in Excel 2000 jayceejay New Users to Excel 3 January 4th 05 06:58 PM
How to change (delivery) days and automatically the receive date in an other cell? Elboo Excel Worksheet Functions 5 November 22nd 04 03:44 PM


All times are GMT +1. The time now is 02:28 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"