To aid in the understanding of my original questsion I thought I would post
my initial attempt at the code. There are more layers that I hope to add,
but I am having issues with the initial setup. There may be a better way to
do this than using the "case" function. I am open to any suggestions.

Here is a copy of my first try - hope it helps to get across what I am
trying to acheive:

''Highlight late Completes

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Deadline As Range
Dim Actual As Range
If Target.Cells.Count 1 Then Exit Sub
Actual = Target
Set Deadline = Range("C20") 'change to suit
Set Actual = Range("D20:H20") 'change to suit

If Not Intersect(Target, Deadline) Is Nothing Then
Select Case Actual
Case Is Deadline
Target.Interior.ColorIndex = 6 'Yellow
End Select
End If
End Sub

Thanks in advance for the help