Jun 13 2018
10:04 AM
- last edited on
Jul 25 2018
09:52 AM
by
TechCommunityAP
Jun 13 2018
10:04 AM
- last edited on
Jul 25 2018
09:52 AM
by
TechCommunityAP
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?
Jun 13 2018 10:18 AM
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.
Jun 14 2018 03:15 AM
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
Jun 14 2018 04:18 PM
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.