Conditional Formatting to Highlight Cells based on specific

Hi. My report needs to automatically change every month. For example rows one and two should change color if 7th month of the year has already passed, then rows 3 and 4 should change color if the 8th month of the year has passed. I'd like to apply this using conditional formatting.

Last year, I have used these formulas. 1111, 2222,3333 and 4444 are lesson code numbers.

=AND($A$8=1111,MONTH(TODAY())>7)
=AND($A$9=2222,MONTH(TODAY())>7)
=AND($A$10=3333,MONTH(TODAY())>8)
=AND($A$11=4444,MONTH(TODAY())>8)

Thank you in advance.

Hi

I have attached a file with the formatting I used previously. What I need the report to show is to highlight lessons not taken based on the schedule. M's are required lessons not
completed yet. those with dates are completed required lessons.

Every month, two lessons should be completed. Since it's March now, lessons that should have been completed before March of this year should be highlighted.

Is there a way to make this automatic?

Many thanks
Jao

Hi

Thank you for your prompt response.

This is the end-of-month report to see if the crew is completing lessons based on the schedule. We actually don't have the Sched sheet on the report. I have included that just for reference. I can't use that sheet as a reference for the formula.

The dates are actual dates. P1 are trainings that are past due dates (ex Lesson 1 and 2 should have been completed last Oct 2020). P2 are trainings that can be completed once P1 have been completed but should be completed before due month is over. like lessons that should be completed this March 2021.

Oct, Nov and Dec are 2020. Jan onwards are 2021.

Basically we want to see on every month's report, what the crew missed to complete (red). And cell with Ms in white are not yet due