Update Excel macro with variable criteria

I have a macro that I run on a daily report. Within the macro code, there is a date that needs to be revised every two months for the macro to be effective. I would like to revise a macro if possible so that instead of having to edit the date in the VBA code every publication cycle, that a reference table is included within the VBA code.

The current macro does the following:

1. Extracts the publication date from the text string in B4 and copies it into $C$5

1. If date in $B$5 is greater than the hard-coded cut-off date of 19-July-17 at 23:00 hrs AND if $K11 does not equal "89" or "99", then replace contents of $D11 with text string = "LATE". If conditions are not met, then no change to $D11 (original text string retained, or if $D11 was blank, then it remains blank).

Current macro code:

Sub ProMS_Transfer_Late_WOs()
Dim lr As Long
Dim rng As Range, cell As Range
Dim compDate As Date
lr = Cells(Rows.Count, "K").End(xlUp).Row
Set rng = Range("K11:K" & lr)
compDate = DateValue("19 July 2017") + TimeValue("23:00:00")
Columns("B:B").ColumnWidth = 5
Columns("D:D").ColumnWidth = 32.43
Columns("F:F").ColumnWidth = 6
Columns("G:G").ColumnWidth = 42.14
Range("C5").Select
ActiveCell.FormulaR1C1 = "=TEXT(MID(R4C2,169,11),""dd-mmm-yyyy"")"
If Range("c5").Value > compDate Then
    For Each cell In rng
        Cells(cell.Row, "D").Select
        If cell <> 89 And cell <> 99 Then
            Cells(cell.Row, "D") = "LATE"
        End If
    Next cell
End If
End Sub

Select allOpen in new window

Desired revisions to current macro:

1. Determine the Cut-off Date for the Pub Date listed in $C$5 by finding the $C$5 pub date in the Pub Date list (see below)
   1. The Cut-off Date associated with each Pub Date is listed in the column to the right of the Pub Date called Cut-off Date, in the same row
2. If report 'Execution' date (B5) is > than the determined Cut-off Date at 23:00 hrs AND if $K11 does not equal "89" or "99", then replace contents of $D11 with text string = "LATE". If conditions are not met, then no change to $D11 (original text string retained, or if $D11 was blank, then it remains blank).

Error handling:
If Publication Date in $C$5 is not found in the Pub Cycle list below, a message box pops up saying 'Pub Cycle date not found'.

Here is the reference table of Pub Dates and associated Cut-off Dates:
Pub Date      Cut-off Date
17-Aug-17      24-May-17
12-Oct-17            19-Jul-17
07-Dec-17      13-Sep-17
01-Feb-18      08-Nov-17
29-Mar-18      03-Jan-18
24-May-18      28-Feb-18
19-Jul-18            25-Apr-18
13-Sep-18      20-Jun-18
08-Nov-18      15-Aug-18
03-Jan-19            10-Oct-18
28-Feb-19      05-Dec-18
25-Apr-19            30-Jan-19
20-Jun-19            27-Mar-19
15-Aug-19      22-May-19
10-Oct-19            17-Jul-19
05-Dec-19      11-Sep-19

I have attached a sample of the report that the macro is run on.

I hope I've provided enough detail and clarity. Just let me know if you need anything more.

Thanks!
Andrea
EE_macro.xls