conditional formatting for multiple columns and count cells based on colors

hi Anita,

I remember you from last time.

you said: Currently, I have 3 columns "quiz 1, quiz 2 and quiz 3" that need to have conditional formatting and each column has its own values and cutoffs. ......

I currently have to go into each column, specify the conditional formatting and it becomes unwieldy when the actual sheet has 10-15 columns like these.

yeah, but that's the easiest way to do it. what's wrong with it doing it manually one time only?

regarding your 1st request: I wonder if there's a vba that allows me to specify the cutoff values for each column in one go? I believe that doing so would be quicker, and by putting all the cutoffs in one place, it's easier for me to eyeball and double check my cutoffs so that I don't accidentally input the wrong cutoff values for the conditional formatting.

if the cutoff values never change, code isn't going to do anything for you. now, if values were constantly changing, obviously code would help. push a button, read the bound limits from the book somewhere and update.

#2: Request #2: is there a way for me to do a count based on the colors that poplate from row 10 in column C, D, and E?

actually I don't think so. there is a function called COUNTIF(), but the arguments don't allow colors to be specified I don't think. because that would be a method inside of a function. but you can do this with code.

Hi Adam,

Thanks for your input. Your points are well-taken and thank you for pointing out that I can't do the request #2 using VBA.

Anita

Originally Posted by anita2017

Hi Adam,

Thanks for your input. Your points are well-taken and thank you for pointing out that I can't do the request #2 using VBA.

Anita

please look at the other response from Ben Van Johnson. he knows more than me about what will work and what doesn't. please post here when you find a solution

Hi protonLeah,

Your solutions are really clever! I think it's such a great idea to insert a column to the boxes at the top specifying the cutoff values and build the formulas from there. The displayed cutoffs make it easy for teachers to see as they currently have to click on each cell to reveal those values and the huge plus is I only have to specify these values once, which gets cumbersome and error prone when I have so many columns to deal with at once.

Now, going to the conditional formatting piece, I have a general understanding of what you're doing and it looks like it will save me tons of hours because I get to apply conditional formatting rules to all columns, rather than doing it one column at a time. If it's not too much trouble, would you explain in plain English what these formulas are saying for each color, or just for yellow?

Thank you again so much for your solutions!

Anita

Hi Ben,

Many thanks for your explanation. I'm learning a lot from your excel formulas and solutions!

Have a wonderful day!
Anita

Hi Ben,

I hope it's okay to ask a follow up question on a solved thread. I'm now testing each part of your conditional formula one step at a time to understand. Broadly speaking, I understand what you did based on your explanation above. However, regarding the match function and its arguments, I see that the reference column and row you entered is C$9. Which part of your entire formula tells excel to look up the value in C$9 and then D$9 and then E$9 in order to return appropriate ranges of scores? I could get excel to return a value of 6 only if I use match(D$9, $1:$1,0) but this is clearly not what you did as all of your formulas in the conditional formatting section only referenced C$9.

Thanks for your guidance!

Anita

Oh I see. Thanks so very much for your expertise!

Anita