need a command button to change / unchange the colour of specified cells

[fingermouse]

Hi,

I'm a complete novice with macros so please forgive me in advance!

Hope someone can help me with the VBA code required.

Please see the attached file...

I've assigned a command button to a macro but I cant get it to perform as required.

What I require the macro to do is this - When the command button is clicked, specified cells change from no fill colour (which should be the state when the excel file is opened) to a yellow fill colour. When I click the command button again, the cells return to no fill colour.

Does that make sense?

Below is the code I have. I know its a load of rubbish, but to be honest I haven't a clue what I'm doing!

Please Login or Register  to view this content.

Any help would be much appreciated!

Best Regards,
Cal

[Kaper]

Check out such modification of your macro:

Please Login or Register  to view this content.

I added static (not volatile) variable mycolor which is assigned to property .color of the "selected" cells interior.
Note that I got rid of .Select .Activate and Selection.

This part

Please Login or Register  to view this content.

could be written much shorter way as:

Please Login or Register  to view this content.

[fingermouse]

That's perfect Kaper, much appreciated!!!

[fingermouse]

If I wanted to modify Kaper's code so that when the command button was clicked, some specified cells (B11,B17,D11,D15,D21) would be coloured yellow but others (B13, D17)would be coloured orange - how would the code be modified?

I require the same functionality (the cell state is initially 'no fill' but when clicked, specified cells change to yellow and orange, then when clicked again returns to no fill)

I'm not sure where I would add the code when introducing the 'orange' variable, as its no longer either 'no fill' or 'yellow'.

Does that make sense?

Any help would be much appreciated!

[Kaper]

Yet another modification:

Please Login or Register  to view this content.

note that I used constants rgbYellow rgbOrange and rgbWhite instead of "strange numbers" and changed type of static constant to boolean.

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

[fingermouse]

Thanks Guys, these both work!

I really appreciate the advice...

[Kaper]

Good point John - most of the code from registered macro is an overhead. And with simple colors colorindex is as good as color (introduced only in ex2007 if I remember)

I would just use only one IF instruction:

Please Login or Register  to view this content.

[JOHN H. DAVIS]

Even a better point. I'm still operating in the dark ages though 2003.

[Kaper]

Heh, I am even more "prehistoric".
In the association I am working part time aside from my main job I use Excel 2000 and ... its

[ranjan82]

Welcome to the Board!

Create a command button and then put in some code like this:

Code:
Private Sub CommandButton1_Click()
selection.interior.colorindex = 6
End Sub
I mean this is as simple as it gets, but it limits it to one color and I don't know if you want to uncolor cells, but I think you want more than this? This will go in the code for the command button.

Hope that helps.