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

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,971
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,971
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,127,744
Messages
5,626,621
Members
416,195
Latest member
tonmcg

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