Give this a try. Make sure you have a backup of your file first.
1. For each sheet you want to colour and check, paste this code in the VB window for that sheet.
<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br> CheckExpiry Target, <SPAN style="color:#00007F">False</SPAN>, Me.Name<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
2. While still in the VB window, double click the "ThisWorkBook" section and paste this code.
<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_BeforeClose(Cancel <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>)<br> <SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet<br> <br> <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> Worksheets<br> CheckExpiry ws.Range("A2", ws.Range("A" & Rows.Count).End(xlUp)), <SPAN style="color:#00007F">True</SPAN>, ws.Name<br> <SPAN style="color:#00007F">Next</SPAN> ws<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_Open()<br> <SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet<br> <br> <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> Worksheets<br> CheckExpiry ws.Range("A2", ws.Range("A" & Rows.Count).End(xlUp)), <SPAN style="color:#00007F">True</SPAN>, ws.Name<br> <SPAN style="color:#00007F">Next</SPAN> ws<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
3. While still in the VB window, use the menu to Insert|Module and paste this code.
<font face=Courier New><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CheckExpiry(<SPAN style="color:#00007F">ByRef</SPAN> myTarget <SPAN style="color:#00007F">As</SPAN> Range, <SPAN style="color:#00007F">ByVal</SPAN> OpenClose <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>, <SPAN style="color:#00007F">ByVal</SPAN> wsName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>)<br> <SPAN style="color:#00007F">Dim</SPAN> Tday <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Date</SPAN><br> <SPAN style="color:#00007F">Dim</SPAN> chRng <SPAN style="color:#00007F">As</SPAN> Range<br> <SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range<br> <SPAN style="color:#00007F">Dim</SPAN> iColor <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br> <SPAN style="color:#00007F">Dim</SPAN> fColor <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br> <SPAN style="color:#00007F">Dim</SPAN> Report(2, 4) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br> <SPAN style="color:#00007F">Dim</SPAN> ReportString <SPAN style="color:#00007F">As</SPAN> String<br> <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br> <br> <SPAN style="color:#00007F">Set</SPAN> chRng = Intersect(myTarget, myTarget.Parent.Columns("A"))<br> <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> chRng <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br> Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br> Tday = <SPAN style="color:#00007F">Date</SPAN><br> <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> 4<br> Report(2, i) = 0<br> <SPAN style="color:#00007F">Next</SPAN> i<br> <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> chRng.Offset(, 1)<br> fColor = xlAutomatic<br> <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> c.Value - Tday<br> <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Is</SPAN> <= 0, ""<br> iColor = xlNone<br> <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Is</SPAN> < 15<br> iColor = 1<br> fColor = 2<br> Report(2, 4) = Val(Report(2, 4)) + 1<br> <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Is</SPAN> < 30<br> iColor = 3<br> Report(2, 3) = Val(Report(2, 3)) + 1<br> <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Is</SPAN> <= 45<br> iColor = 6<br> Report(2, 2) = Val(Report(2, 2)) + 1<br> <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Is</SPAN> > 45<br> iColor = 4<br> Report(2, 1) = Val(Report(2, 1)) + 1<br> <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Else</SPAN><br> iColor = xlNone<br> <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN><br> c.Interior.ColorIndex = iColor<br> c.Font.ColorIndex = fColor<br> <SPAN style="color:#00007F">Next</SPAN> c<br> <SPAN style="color:#00007F">If</SPAN> OpenClose <SPAN style="color:#00007F">Then</SPAN><br> Report(1, 1) = "Good = ": Report(1, 2) = "Near = "<br> Report(1, 3) = "Need to Replace = ": Report(1, 4) = "Bad = "<br> ReportString = "For Sheet " & wsName & vbLf & vbLf<br> <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> 4<br> ReportString = ReportString & Report(1, i) & Report(2, i) & vbLf<br> <SPAN style="color:#00007F">Next</SPAN> i<br> Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br> MsgBox ReportString<br> <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br> Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br> <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
4. Close the VB window, save the Workbook and try changing values in column A (you still need the formulas in column B), closing the Workbook, re-opening the Workbook.