Command Buttons for Fill Colour and Font Colour

Hi all,

I am really struggling trying to put a template together and would be very grateful for some help.

Please see attachment.

I want to use the four command buttons as follows:

When a user selects any range (one or more cells) and then clicks a Fill Colour or Font Colour command button, I want all the rows for the selected range, from column A to column AC, to be highlighted accordingly – that is, fill colour or font colour. The command buttons must toggle – to (1) colour and (2) remove colour.

Recalculate sheet only when there is a change in column X.

Row 1 has the command buttons, and row 2 is the header row.

Appreciate your help.

Thank you.
Gos-C

Hi venkat1926,

Thanks for your response.

I would like only the range A:AC of the selection fill - instead of the entire row in the following lines:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Can you help, anyone?

Thanks,
Gos-C

In Excel 2003, you can tear off the Fill Color menu so it sits on the sheet (click the dropdown, and when the toolbar appears, grab the top and drag it onto the sheet. Then you have single-click access to any fill color, or none.

Hi Shg,

I am (we are) using Excel 2007, but I posted the sample spreadsheet in Excel 2003 format in order to accommodate anyone who was not using Excel 2007. Furthermore, I need to use VBA to ensure that everyone who uses the template applies the exact colour because the colour would be used to sum/count in the data.

I want to work with columns A to AC only - not the entire row,

Thanks,
Gos-C

You can use something like:

Please Login or Register  to view this content.

because the colour would be used to sum/count in the data

Although Excel 2007 allows for summing by color, I strongly advise against that technique. Color is not data (Mike Alexander). Use a helper column with hard and fast data instead.

=if(condition,"Yellow","pink")

or some such, and then use sumif or sumifs or a pivot table to sum up the data. This way you ensure that your data is version independent, portable and makes sense when printed out in black and white.

Then use a simple sum

romperstomper, the code works on one row only - ever if more than one row is selected.

teylyn, yes, I will be summing values in column X if the fill colour of the row is yellow.

How can I have the code highlight all the rows in the selection?

Thanks,
Gos-C

I believe that I have figured it out:

Please Login or Register  to view this content.

Thanks for your help, everyone.

Gos-C

Hi, and welcome to the forum.

Unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.