Keyboard Shortcut to Toggle between Various Formats (e.g. font & fill color, numbering)

[BIGDOG123]

I am building some shortcuts into excel and am wondering how do I make a keyboard shortcut that can toggle between different formats. I would like to create several toggles highlighted below and hopefully more thereafter:

- Toggle Between Colors with “Cntrl + Shift + C” (Blue, Green, Red)
- Toggle Between Fill Color with “Cntrl + Shift + Y” (Yellow, Grey, Beige)
- Toggle Between Number Formats with “Cntrl + Shift + N” (Number, Currency, Percent, Multiple(ie: 5.0x ) )

Also, when I create my own keyboard shortcuts, I cannot press “Cntrl + Z” to undo them. Is there something I can do to the code to allow me to press “Cntrl + Z” to undo my keyboard macros. For example, assuming I can get the above to work, If I press “Cntrl + Shift + C” to get a black cell to turn blue, then I would like to be able to press “Cntrl + Z” to undo it back to black if necessary.

 

[AkaTrouble]

just so everyone knows this is cross posted on another forum

Keyboard Shortcut to Toggle between Various Formats (e.g. font & fill color, numbering)

BIGDOG123

if you post to more than one forum it is best to let everyone know so before they put any effort into solving your problem they can check it not already solved by someone else

i notice you have at my time of posting recieved no replies to either

 

[BIGDOG123]

Apologies - I should have clarified. Given little response in the other forum, I thought I would try it in Mr. Excel.

 

[Oaktree]

Your question implicitly has three parts:

1) Write a macro that finds the current format, determines what the next one in the sequence should be, and changes the format to that.

2) Assigns the macro from (1) to a specific keyboard shortcut. This is the easy part, as you just press Alt+F8 to bring up the macros window, click the macro you want, click "options" and choose the shortcut key to assign to the macro.

3) For undo, the short answer is "no", you cannot undo a macro... but, you could just make a new macro that does the same thing as (1) but in the reverse order.

Have you experimented with (1) already? Where are you stuck?

 

[BIGDOG123]

Thank you for the reply. I'm somewhat new at macros and have tried to record a macro to see the code that it creates, but I'm getting stuck on writing the correct code that results in a toggle, meaning the font color goes from black, red, blue, green and back to black. I've done some research that references a "Case Range" but haven't found anything else that is helpful. Any guidance and / or basis would be extremely helpful! Thanks in advance for your help.

 

[Oaktree]

Here's the basic construct:

Sub change_color()

Select Case ActiveCell.Interior.Color

Case vbBlue
ActiveCell.Interior.Color = vbGreen

Case vbGreen
ActiveCell.Interior.Color = vbRed

Case vbRed
ActiveCell.Interior.Color = vbBlue

End Select

End Sub

If you want to use different blue/green/red than what the vb named ones refer to, you can always start recording a macro and change the fill color to find the right color values.

Number formats would be similar. Instead of .interior.color, you'd reference the .NumberFormat property. Again, the macro recorder will be your friend for finding the right formats to use.

 

[BIGDOG123]

Thanks for the guidance. I tried that macro and unfortunately didn't result in the font color of the cell changing colors. Any other ideas?

 

[Oaktree]

The code I posted will work with the fill color. To work with the font color instead, use the same principle but with Activecell.font.color instead of Activecell.interior.color.

 

[BIGDOG123]

Neither the fill nor font color changed when I used the macros. Could some additional code be missing?

 

[Oaktree]

Probable cause is that the current font/interior colors aren't one of the options listed in the case selection of your code.

As a quick test, try running this snippet of code:

MsgBox "Active cell's font color is " & ActiveCell.Font.Color & Chr(10) & _
       "Active cell's interior color is " & ActiveCell.Interior.Color & chr(10) & _
       "vb blue is " & vbBlue & Chr(10) & _
       "vb green is " & vbGreen & Chr(10) & _
       "vb red is " & vbRed