willara23
06-30-2016, 05:36 PM
Excel 2010 version: 14.0.7015.1000(32-Bit)
I have referred to detail offered at 'https://msdn.microsoft.com/en-us/lib...ice.12%29.aspx'
The code works as I hoped for the range "N2:N300", though I can only implement one condition successfully for the next range "O2:O300". If I attempt to implement both conditions on the range O2:O300, one format appears to be assigned to the other condition, while the other format is not applied.
Initially I thought I had not applied mutually exclusive logic in the formula. I believe I have the mutual exclusivity correct, which I verified on sheet "Test mutual exclusivity".
Clearly there is an error and or a lack of understadning on my part. I have attempted to solve the issue over days of this week, and searched various web resources with out success.
I would appreciate any guidance in resolving issue, or if not possible at least understand the limitation related to the expectation.
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
I have referred to detail offered at 'https://msdn.microsoft.com/en-us/lib...ice.12%29.aspx'
The code works as I hoped for the range "N2:N300", though I can only implement one condition successfully for the next range "O2:O300". If I attempt to implement both conditions on the range O2:O300, one format appears to be assigned to the other condition, while the other format is not applied.
Initially I thought I had not applied mutually exclusive logic in the formula. I believe I have the mutual exclusivity correct, which I verified on sheet "Test mutual exclusivity".
Clearly there is an error and or a lack of understadning on my part. I have attempted to solve the issue over days of this week, and searched various web resources with out success.
I would appreciate any guidance in resolving issue, or if not possible at least understand the limitation related to the expectation.
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