Conditional Formatting Letter Grades - "Minuses" Not Working

diderooy

New Member
Joined
Jan 9, 2014
Messages
16
Hello,

I'm sure it's out there somewhere but I haven't been able to find it yet. So sorry, everyone, I'm pulling you into this.

I have created a worksheet of letter grades and would like to do a conditional format color fill of the cells for every letter grade in the same cell. These grades include pluses and minuses. I have all the rules generated as "Format only cells that contain" "Specific Text" "containing" "A+" as an example; "A" or "A-" would be the others for that letter, there are no quotation marks, spaces or other characters in that field of the rule. All of them seem to work correctly except for the minuses, which seem to filling with the A/B/C/D (no pluses or minuses). I have listed my RGB color fills below:

F 255, 209, 209
D- 255, 220, 194
D 255, 230, 179
D+ 255, 238, 189
C- 255, 247, 199
C 255, 255, 209
C+ 247, 255, 200
B- 239, 255, 191
B 231, 255, 183
B+ 222, 255, 190
A- 213, 255, 197
A 204, 255, 204
A+ 198, 255, 215

So the A- grades fill with the same color as the A grades, the B- grades are the same color as the B grades, etc. But the "plus" grades are distinct in color from the others of their letter.

What am I doing wrong?

EDIT: All the letter grade cells are formatted as "General" type, which I didn't think would matter but I don't know.
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,574
Office Version
  1. 365
Platform
  1. Windows
Make sure that the rule for A- & A+ are above the rule for A the same for the other letters.
Alternatively change the rules to use
1610137923379.png
 

R2ah1ze1l

Board Regular
Joined
Nov 10, 2017
Messages
67
It appears after some testing this is order related:
+/-
+/-
Letter only

Hope that helps you, just arrange your solid letter rules to the bottom of your list.
 

Attachments

  • Capture.JPG
    Capture.JPG
    55.3 KB · Views: 4

diderooy

New Member
Joined
Jan 9, 2014
Messages
16
Make sure that the rule for A- & A+ are above the rule for A the same for the other letters.
Alternatively change the rules to use
It looks like this solved my problem on its own! What does changing the rule type affect (like your screenshot) do?

Thanks very much for your help! I didn't realize (or forgot) that ordering the rules might actually impact the way they're applied...I had just been thinking they were arrangeable for comparison's sake.

A belated Happy New Year :)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,574
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Because you are using "Contains" it will look to highlight A, A+ & A- because they all contain A, so the the order of the rules matters.
However if you use Equals, the order of the rules won't matter as A does not equal A- or A+
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,574
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,653
Messages
5,626,105
Members
416,161
Latest member
David1966Lewis

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