Conditional Formatting in a Group on a Report

[vanguardaccess]

I have a report with data such as the following:

Name ---- Course ----- Dept ----- Grade

John ----- Algebra ---- Math ----- 77
John ---- Geometry --- Math ---- 81
John ---- Sculpture --- Art ------ 99
John ---- Painting ---- Art ----- 59

The report is grouped by department so that all the math courses appear in
one group, art in another, etc.

In the group footer, I have already created a box that shows the number of
courses passed per department (>=65) as well as the number of courses
required to complete in that department. For example, students must pass 3
art courses to graduate. So, based on the data above a message tells them
that they have passed 1 course out of the 3 required. The required number is
done by referencing a table that has departments and required number of
courses.

What I would like to do now is conditionally format text boxes so I can make
it look more graphical. The part that I am stuck on is how to conditionally
format using criteria from 2 separate fields. For example, there should be 3
boxes in the Art group, and 8 in the math group. Then, the additional
condition is if the student passed 1, the first box should turn green. If a
student passed 2, the first and second box should turn green. I know how to
set these conditions, but not how to set the number of boxes that show up
depending on the number of courses required to take in the department.

I hope this is clear. Thank you.

 

[vanguardaccess]

I really appreciate your help. I don't think I have the technical expertise
yet to fully understand what you said, and therefore need more guidance.

In the Event tab under format, I believe you are saying that I need to put
the expression that you wrote. How do I make sure that it will apply only to
the 10 text boxes that I put in and not other things in the group footer?

Also, I have put 10 text boxes. Do the labels matter? What expressions
should I give them?

Finally, I don't understand a few parts of the code. What do "dim" and
"me." mean?

I appreciate your help very much.

 

[vanguardaccess]

I've been looking at the situation more closely. If you can answer the
following question, I think I may have a solution.

Is there a way to create a text box that is visible only on the condition
that another field's value is equal to a certain number? If so, how?

For example: I have a field called [Required_credits]
My text box gets its value from another source
(=Sum(IIf([Vangrade]>=1,1,0)). However, I would like it only to be visible
when [Required_credits] is greater than or equal to 4.

Thanks.