Forum Discussion

cliff brock's avatar
cliff brock
Brass Contributor
Jun 13, 2018

Cell color with button

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?

Charts & Visualizing Data
Requests

4 Replies

  • Matt Mickle's avatar
    Matt Mickle
    Bronze Contributor
    Jun 13, 2018

    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.

    • cliff brock's avatar
      cliff brock
      Brass Contributor
      Jun 14, 2018

      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

       

       

      • Matt Mickle's avatar
        Matt Mickle
        Bronze Contributor
        Jun 14, 2018

        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.

         

         

         

         

Resources