Change formatting based on new date input

Alan Z

Background

Currently, I am tracking whether a person attended a visit on a certain date and color coding for easy reference. Each row represents a person

These dates are at regular intervals, so I'm using a column formula to calculate what the dates should with each each column referencing the column before it as long as the person is active(note the first few columns have a slightly different interval)

Example: =IF ([A]@row = "Not active", "", IF ([A]@row <> "", [A]@row + 28, ""))

I then use condition formatting to highlight any date in the past in green.

Need

At some point, I would like to highlight the date of the last visit. I would like to enter in the date we are informed the person will no longer attend and then reference that in order to change the formatting (red for last visit, green for all prior visits, ideally blank out all future visits).

Getting stuck on how I can do the above, but any solution that adjusts the formatting so I can highlight the last visit is appreciated. Picturing something like this:

Paul Newcome

Try this to output the dates:

=IF(AND(A@row <> "Not Active", A@row <> "", A@row + 28 <= [Stop Date Column Name]@row), A@row + 28)

A helper column will be needed for the conditional formatting. Use a nested IF to output a different letter based on which date is the last.

=IF([1st Date]@row = "", "A", IF([2nd Date]@row = "", "B", IF([3rd Date]@row = "", "C", …………………

Then you would set up separate conditional formatting rules for each column basically highlighting the appropriate column based on the letter in the helper column.