One format button to run multiple format buttons on different sheets

[PsychicFish]

hey everyone, I am much better at formulas than I am at VBA so any help you can give me will be much appreciated!

I have two VBAs each with it's own button on 10 different sheets. so on sheet one i have button one (my first VBA) and button 2 (my second VBA). I would like a master button to run all of the button 1's first then another master button to run all of the button 2's (unless all button 1's can be ran then button 2's with one master button, that would be amazing!)

I have attached the spreadsheet to this thread. The Overdue Reports Tab has my master buttons with a faulty VBA right now to run all my format buttons. The 10 tabs that end in "-R" will be all of the button 1's and button 2's I talked about above.

The "-R" tabs have the macros on their tabs but I have included the macros in this thread as well.

I hope this all makes sense please let me know if you need me to further clarify, Thank you!

Code for button 1's: (Note: the Set SourceSht/TargetSht are different for each tab)

Please Login or Register  to view this content.

Code for button 2's: (Note: the Set r = is different for each sheet)

Please Login or Register  to view this content.

[antoka05]

You could use this code for the first button:

Please Login or Register  to view this content.

And this code for the second butto:

Please Login or Register  to view this content.

Regards,
Antonio

[Greg M]

Hi there,

A question:

The "ifGreenMakeCellValueEQ1" routine needs to be run once for each "-R" worksheet (i.e. ten times), but the "CopyColor" routine seems to involve changes to only the "Mil Pay-R" worksheet - can this routine be run just once?

Regards,

Greg M

[Greg M]

Hi again,

Take a look at the attached workbook and see if it does what you need.

It uses the following code:

Please Login or Register  to view this content.

The highlighted values can be changed to suit your own requirements.

Click on the "Format All Worksheets" button on the "Overdue Reports" worksheet to run the above routines.


Hope this helps - please let me know how you get on.

Regards,

Greg M

[PsychicFish]

Greg M, I saw your code and I have no clue how you did it but it is the most beautiful thing I have ever seen! It works perfectly thank you!

Can you explain the "7" and "D" highlighted fields above so if I adjust them I know what I am adjusting. Thank you again!

[Greg M]

Hi again,

It's feedback like yours that keeps me coming back to this Forum - many thanks

The "D" value refers to Column D, i.e. the first column in the range to be formatted.

The "7" value refers to Column 7 (Column G), i.e. the last column in the range to be formatted.



It wasn't until I read your question that I realised I'd used an inconsistent approach by referring to the first column by its letter and the second column by its number. The attached workbook uses a consistent approach - each of the above columns is referred to by its letter. The "IfGreenMakeCellValueEQ1" routine has been re-written as follows:

Please Login or Register  to view this content.

Hope this helps - as before, please let me know how you get on.

Regards,

Greg M



P. S. Please feel free to shout if you need any further explanation of the code.