Percentage Color Coding Question

ATY807

New Member
Joined
Mar 18, 2020
Messages
11
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: 2
  • Percentage Column.jpg
    Percentage Column.jpg
    21.1 KB · Views: 3

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,985
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
11
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: 2

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,985
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,146
Messages
5,640,392
Members
417,140
Latest member
whiteprose

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