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 a...
Charts & Visualizing Data
Requests
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.

 

 

 

 

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

    thanks. I learn something new everyday.

     

    cliff

Resources