Percentage Color Coding Question

ATY807

New Member
Joined
Mar 18, 2020
Messages
12
Office Version
  1. 2013
Platform
  1. Windows
I have a column of numbers (in percentages) which is a part of a pivot table. I need to color code this column as follows:

- Percentages above 95% "Good"
- Percentages >= 90% and lower than 95% "Neutral"
- Percentages < 90% "Bad"

I would like to use Excel Styles for colors.

I have written below code:

Sub Percentcolorcode()
Dim Score As Integer
Score = ActiveCell.Value
Select Case Score
Case Is >= 0.95
ActiveCell.Style = "Good"
Case Is >=0.9 and <0.95
ActiveCell.Style = "Neutral"
Case Is < 0.9
ActiveCell.Style = "Bad"
End Select
End Sub

It gives me an error for the line Case Is >=0.9 and <0.95. When I remove that line, I would still expect that it runs for the two other conditions, but it only color codes one cell from the selected column and that is with the wrong color.

Could someone please help with this code.

Thank you,
 

Attachments

  • Excel Styles.jpg
    Excel Styles.jpg
    15.1 KB · Views: 3
  • Percentage Column.jpg
    Percentage Column.jpg
    21.1 KB · Views: 4

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,208
The < .95 is implied, since if the cell value were >= .95, the prior branch would have exicuted.
You can not use AND within the Case line
Try
VBA Code:
Select Case Active.Cell.Value
    Case Is >= .95
        MsgBox "big"
    Case Is >= . 9
        MsgBox "not quite so big"
    Case Is < .9
        MsgBox "bad"
End Select
 

ATY807

New Member
Joined
Mar 18, 2020
Messages
12
Office Version
  1. 2013
Platform
  1. Windows
The < .95 is implied, since if the cell value were >= .95, the prior branch would have exicuted.
You can not use AND within the Case line
Try
VBA Code:
Select Case Active.Cell.Value
    Case Is >= .95
        MsgBox "big"
    Case Is >= . 9
        MsgBox "not quite so big"
    Case Is < .9
        MsgBox "bad"
End Select
Thanks for your response Mike. Your template does resolve the error resulting from the use of AND within the Case command, but I still have the same issue, it only applies the color coding to one cell, and with wrong color. See the example in the attached image where 91.90% is supposed to be marked with Neutral cell style, but it's colored green.
 

Attachments

  • Example 2.jpg
    Example 2.jpg
    5 KB · Views: 3

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,208
That code only addresses the Active Cell and the color it is supposed to make the cell. If you want a different color, change the color of the Style to match your desire.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,944
Messages
5,834,513
Members
430,291
Latest member
sunilbalan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top