how to automate conditional formatting and reverse conditonal formatting

Hi all,

I do not know anything about VBA in excel, but I'm afraid that what I need to accomplish requires knowledge of VBA. Given that I spent days creating these templates column by column, I thought it may be worthwhile to see if you could shed some light on how to automate some of these very tedious, time-consuming, and error-prone tasks.

I attached the excel workbook for your review.

Quick description: on the first sheet, "fall data", teachers are supposed to enter the students' scores for all the quizzes. Depending on what time of the year, the number of quizzes can go from 3 to 8.

On the second sheet, "fall test results", starting from row 10, it includes conditional formatting for those scores that were previously entered in the first page. For example, student 1, has scores of 15, 1, and 4 for quiz 1, 2, and 3, and because of the pre-specified cutoffs I created manually for each quiz, that kid has pink, yellow, and green to indicate how he/she is meeting expectations.

At the top of the second sheet are the boxes that summarize many students and percent of students in my classroom that met expectations or not for all quizes. In creating these columns, I once again had to specify the cutoffs for the countif function.

As you can see, each quiz has its own cutoffs and there're many quizzes, and doing this manually is very time-consuming and extremely error-prone.

I would like to know if: (1) there's a quicker way of automating the conditional formatting for each quiz. Right now, I woudl go to conditional formatting, hightlight cell rules, select "between", specify the cutoffs and select format with "light red fill with dark red text". I kept going until I specified all the cutoffs and the colors I wanted for each quiz column.

(2) I would also like to know if, for the summary boxes at the top, instead of once again re-especifying the cutoffs within the countif function in column B, that I can do something like, for quiz 1 and row 6, if it's light red fill with dark red text, count it, for quiz 1 and row 5, if it's yellow fill with dark yellow text, count it, etc. In other words, I wanted to do something like reverse conditional formatting. If this can't be done but you have other clever ways of automating these boxes, I would love to hear them too!

I would appreciate your assistance and expertise on this very much. Thank you!!

Sincerely,

Anita

Hi

Also not an expert, but why not use pivot tables as i did in the attached? You can then just add data as you wish, click refresh on the pivots and it will auto update. You can also apply your conditional formatting to the pivot and it will then auto update.

Sorry, i did not complete the pivots for you, but i have limited time.......

Here is a useful article about building pivots in case you have trouble with it.

HTML Code: 

Hi,

Thank you for your quick response and your suggestions. Unfortunately, these templates are designed to be used by teachers, and they have to be in the format that I provided so that all the teachers have to do is complete the data entry on sheet 1 and these templates would provide the summary statistics for them on sheet 2.

The pivot tables you suggested, while they're useful in telling me how many students completed each quiz and how many students obtained a certain scores, they don't give me a break down of the percentage of students who met expectations, or were below expectations for all the students who completed a certain quiz. All of this would require that I once again have to specify the cutoffs specific to each quiz, which is what I'm trying not to do. Plus, like I said, the templates I'm giving to the teachers have to look like the 2 sheets I provided.

I was hoping for another more efficient way of accomplishing what I did, given that the format and layouts have to look the same or at least very similar.

Thank you and I welcome additional suggestions from you and everyone else.

Anita