Toggle cell fill

Hi, ballan,

maybe like this:

Please Login or Register  to view this content.

Ciao,
Holger

Thanks, but just looking at it, looks like it would turn a white fill to cyan, but not a cyan fill back to white?
But I do see my many other syntax errors. Will try to modify the Else part.

Never mind. I am confused, but it works as you did the code, without needing the second If/Then. I have lots to learn.
Thanks again.

Hi, ballan,

as long as you want only 2 colours in a range it´s pretty easy: the cell has the given CI (ColorIndex) than switch, or if not, give that to the cell.

Please Login or Register  to view this content.

is just a definition of a range but Excel doesn´t know what to do with it. There must be any action (Value is the Standard for the range but that would need any value to be put in).

The first End Sub will end the procedure, and that will cause the compiler to claim that there´s an If without an End If.

I have lots to learn.

You can put me on that list as well - I´m still learning about the Application Model of Excel2000, and a lot has changed in the later Versions.

Ciao,
Holger

Guten Nacht Holger,
Your code worked great, except when I tried to copy it to work for my second range with a different color I get this error:
Ambiguous name detected: Worksheet_SelectionChange.
Hope you are still up tonight!

Hi, ballan,

behind each sheet there may only be one event of the very same name. So here we try your Else-Case (please change the range address and colour as needed):

Please Login or Register  to view this content.

HTH
Holger

Amazing!

I am learning more here than the 475 of 1020 pages I have read in Walkenbachs "Power Programming With VBA"
I don't want to become a burden. But here is one more challenge:

If I wanted to make sure my double sort (color and number) could not be done ( pressing the command button would open a message box with text "Must select five", for example) unless 5, and only 5 cells had been filled with color, what would you add to the code?

Amazing!

I am learning more here than the 475 of 1020 pages I have read in Walkenbachs "Power Programming With VBA"
I don't want to become a burden. But here is one more challenge:

If I wanted to make sure my double sort (color and number) could not be done ( pressing the command button would open a message box with text "Must select five", for example) unless 5, and only 5 cells had been filled with color, what would you add to the code?

Hi, ballan,

duplicate post to #8 - I think I need not repost #9, do I? Or did you already test it (with my not that good knowledge of the english language while creating some "new" words)?

Ciao,
Holger

So this would be inserted after the "Private Sub . . . " declaration?

Hi, ballan,

if you want it there then yes. But I restricted the selection in that event to only 1 cell because otherwise you have to check for the areas of the selection and the number of cells therein and according to my knowledge loop through every single cell of the selection. That would mean a different code than the one I posted, and I need to know how this code shall be started: if 5 cells within the given ranges are selected? Or if any cell is within any of the given ranges?

Ciao,
Holger

Hi, balan,

from what I read from your last post I think the SelectionChange-Event might not be the best solution available. I prefer the Worksheet_BeforeDoubleClick or Worksheet_BeforeRightClick and suppress their default behaviour by using Cancel = True in the code.

If I understand the problem correctly you want to select any given number of cells and want to change the colour for those cells. There are 2 ranges where this action has to be allowed. If the colouring has taken place there should be a count to find out if there are 5 cells filled with the colour and then sort the range (could you supply the code for that, please - I know how to sort in 2010 but I need your ranges and criteria).

I would probably use a tab on the ribbon to start the action from instead of using an event driven approach.

Ciao,
Holger

Hi Holger,

I am up at 4AM here. I have been thinking about this all night. Your code for changing the cell color is perfect. I am very grateful for your help.

It is the code for the sort command button that needs to count the number of cells in the range that have been filled with color. There are two command buttons - one for each color. If that number is not five when the sort command button is clicked, then the sort should not be done and a message box should appear with text = "Must select five." So I assume I need to add code to do this count at the beginning of the sort code for the command button. And if the count does not equal five, the subroutine ends and displays the message box. This is just an error handling routine. Most of the time, the person should remember to pick five items without help!

I will send the sort command button code later. I need to figure out the correct way to do that. I was warned by the moderator that I need to add tags to my code?

Here is the sort code I created for one of the two command buttons when I used the macro recorder.
I realize it is probably much longer than required.

Please Login or Register  to view this content.

Thanks for all your help on this Holger. This latest change does not seem to work. I installed the code for HSORT button and it no longer does anything. No message box no matter the number of cells filled, and no sorting. But I am happy with the function without forcing the need to have only 5 cells filled. Thanks again.