Cell color with button

Copper Contributor

I have a pivot table we use for registration. I want to use a macro to change the cell color (yellow for contracted, red for committed). I would then use a button, so users (who are not excel savvy at all) can just click the red or yellow button. Then, we sort by color to get our volunteer report organized. I am OK in excel, but not in vba. I use the macro recorder and modify code when able. Can someone help me with the few lines of code to use to turn a cell a given color?

4 Replies

Hey Cliff-

 

Would you be able to share a non-sensitive example file with the PivotTable and dataset for reference?  If you can't provide the exact datset maybe mock up 10 or 20 rows of data that will allow you to make a the same pivot.  If you can provide this further context it may be a little easier for the community to assist with your issue.

Thank you for the quick response. I fooled around with it late last night and got what I needed. Here's what I used.

 

Thanks again. This forum is a great resource.

 

cliff

 

 

Hey Cliff-

 

Glad you were able to get your issue resolved.  Here are a few tips.  Some of that stuff from the macro recorder isn't necessary.   The portion that says 

 

'For Example
.TintAndShade =0.59999389629298105
'is trying to round to 0.6 or 60%
.TintAndShade =0.6

You can also work with ranges directly if you know what they are.  You could shorten the one snippet to this most likely:

 

'How to work with the range directly...    
With Range("A1").Interior .ThemeColor = xlThemeColorAccent2 .TintAndShade = 0.6 End With 'or if you need it to be the selection With Selection.Interior .ThemeColor = xlThemeColorAccent2 .TintAndShade = 0.6 End With

Hope this helps.

 

 

 

 

thanks. I learn something new everyday.

 

cliff