Sub CFR()
On Error Resume Next
Dim Sht As Worksheet: Set Sht = Sheets("R")
Application.EnableEvents = False
Application.ScreenUpdating = False
With Sht
.Activate
With .Range("N2:N300") 'Red for MCED <=5 days to expire
.Activate
'set cell to red when the DOI field isblank
'IF(AND(OR(D15="p",D15="w"),OR(ISBLANK(E14),E15="opt")),TRUE,FALSE)
'.FormatConditions.Delete
'.FormatConditions.Add xlExpression, Formula1:="IF(AND(OR(J2=""p"",J2=""w""),OR(ISBLANK(N2),N2=""opt"")),TRUE,FALSE)"
'.FormatConditions(1).Priority = 1
'.FormatConditions(1).Interior.ColorIndex = 3
'.FormatConditions(1).Font.ColorIndex = 1
'set cell to no colour when the days difference between the DOI and today is <90
'.FormatConditions.Delete
'.FormatConditions.Add xlExpression, Formula1:="=IF(AND(OR(J2=""p"",J2=""w""),(NOW()-N2)<90),TRUE,FALSE)"
'.FormatConditions(1).Priority = 2
'.FormatConditions(1).Interior.ColorIndex = 2
'.FormatConditions(1).Font.ColorIndex = 1
'set cell to red when the days difference between the DOI and today is >=90 and <180
.FormatConditions.Delete
.FormatConditions.Add xlExpression, Formula1:="=IF(AND(OR(J2=""p"",J2=""w"",J2=""r"",J2=""n""),AND((TODAY()-N2)>=90,(TODAY()-N2)<180)),TRUE,FALSE)"
.FormatConditions(1).Priority = 1
.FormatConditions(1).Interior.ColorIndex = 6
.FormatConditions(1).Font.ColorIndex = 1
'set cell to red when the days difference between the DOI and today is >=180 and <365
.FormatConditions.Add xlExpression, Formula1:="=IF(AND(OR(J2=""p"",J2=""w"",J2=""r"",J2=""n""),AND((TODAY()-N2)>=180,(TODAY()-N2)<365)),TRUE,FALSE)"
.FormatConditions(1).Priority = 2
.FormatConditions(2).Interior.ColorIndex = 44
.FormatConditions(2).Font.ColorIndex = 1
'set cell to red when the days difference between the DOI and today is >=365
.FormatConditions.Add xlExpression, Formula1:="=IF(AND(OR(J2=""p"",J2=""w"",J2=""r"",J2=""n""),(TODAY()-N2)>=365),TRUE,FALSE)"
.FormatConditions(1).Priority = 3
.FormatConditions(3).Interior.ColorIndex = 3
.FormatConditions(3).Font.ColorIndex = 2
End With
With .Range("O2:O300") 'Case age from DOI Red >=365 days, 365>Amber>=120, 120>Yellow>=90
.Activate
.FormatConditions.Delete
.FormatConditions.Add xlExpression, Formula1:="=IF(AND(OR(J2=""p"",J2=""w"",J2=""r"",J2=""n""),AND((O2-INT(TODAY()))<=5,(O2-INT(TODAY()))>=0)),TRUE,FALSE)"
.FormatConditions(1).Priority = 1
.FormatConditions(1).Interior.ColorIndex = 44
.FormatConditions(1).Font.ColorIndex = 1
.FormatConditions.Add xlExpression, Formula1:="=IF(AND(OR(J2=""p"",J2=""w"",J2=""r"",J2=""n""),(O2-INT(TODAY()))<0),TRUE,FALSE)"
.FormatConditions(1).Priority = 2
.FormatConditions(1).Interior.ColorIndex = 3
.FormatConditions(1).Font.ColorIndex = 2
End With
End With
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub