Excel macro coding , when i click on a cell i want it to change through a range of colors i have pre selected?

what i want to do is when i click on a box i want it to change colour, if you click it again it will change to another colour. these colours i want to pre select, green, blue, yellow, pink, red. once it gets to the end of the colours it gose back to the start with the colour white

Excel macro coding , when i click on a cell i want it to change through a range of colors i have pre selected?
I would use selection, and a keyboard shortcut. Actually running the macro every time you click a cell would be very annoying. It would make the spreadsheet much more difficult to work with: like changing values in the grid without the colors changing could be tricky for the average user. Unless you want a program for a practical joke (which would actually be kind of funny) I'd avoid the click cell thing.





But to do what you want, just make an array that has your color names and cycle through the array, using % array_size to handle array index overflow.





Then increment the value for that cell. Basically you figure out what color it is now, find that in your array, and make the color of the cell the next value, remembering that the next value could wrap around to the beginning.
Reply:You can't click on a cell directly and have it respond, but you can install a command button on the sheet.





Type in the following declaration (Dim) and macro into a module in the Excel VB Editor (Alt-F11)





Dim j As Long





Sub ColorChange()


   Dim bColor(5) As Long





   bColor(0) = vbRed


   bColor(1) = vbYellow


   bColor(2) = vbBlue


   bColor(3) = vbGreen


   bColor(4) = vbMagenta


   bColor(5) = vbCyan





   Range("$B$2").Select


   With Range("$B$2").Interior


      .color = bColor(j)


      .Pattern = xlSolid


   End With


j = (j + 1) Mod 6





End Sub





This macro will change the cell B2, so change it to whatever cell (or range) you want. Then install a command button on the sheet and assign this macro to it.





To install a button, click on View, Toolbars, Forms.


Click on the button icon and draw a button on the sheet