Hello msiyab,
I do not know how to do this with a formula. I think it is possible to do with a formula, but I do not know how to do it.
Here is VBA way, for
Originally Posted by
msiyab
Column A = Cheque Date
Column N = Clearing Date
I want the Conditional Formatting to highlight Column A if the below criterias are met.
1. Column N is blank. 2. If current date is 150+ days than the Column A date.
I need this to know if a cheque is nearing its expiry date (6 months).
If column N = "" And Today - Column A => 150 Then interior color is yellow
Alan
_.______________________
Before:
_____ Workbook: CheckCheque.xlsm ( Using Excel 2007 32 bit )
| Row\Col |
A |
B |
C |
D |
E |
F |
G |
H |
I |
J |
K |
L |
M |
N |
O |
|---|
1 |
12.09.2018 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
2 |
13.09.2018 |
|
|
|
|
|
|
|
|
|
|
|
|
07.02.2019 |
|
3 |
12.09.2018 |
|
|
|
|
|
|
|
|
|
|
|
|
07.02.2019 |
|
4 |
11.09.2018 |
|
|
|
|
|
|
|
|
|
|
|
|
07.02.2019 |
|
5 |
10.09.2018 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
6 |
09.09.2018 |
|
|
|
|
|
|
|
|
|
|
|
|
07.02.2019 |
|
7 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
_____ Workbook: CheckCheque.xlsm ( Using Excel 2007 32 bit )
| Row\Col |
A |
B |
C |
D |
E |
F |
G |
H |
I |
J |
K |
L |
M |
N |
O |
|---|
1 |
=TODAY()-148 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
2 |
=TODAY()-147 |
|
|
|
|
|
|
|
|
|
|
|
|
=TODAY() |
|
3 |
=TODAY()-148 |
|
|
|
|
|
|
|
|
|
|
|
|
=TODAY() |
|
4 |
=TODAY()-149 |
|
|
|
|
|
|
|
|
|
|
|
|
=TODAY() |
|
5 |
=TODAY()-150 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
6 |
=TODAY()-151 |
|
|
|
|
|
|
|
|
|
|
|
|
=TODAY() |
|
7 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Run routine:
Code:
Option Explicit
Sub ChkChqe() ' http://www.excelfox.com/forum/showthread.php/2301-Conditional-Formatting-of-Dates-(Multiple-Criteria)
Dim Ws1 As Worksheet
Set Ws1 = ThisWorkbook.Worksheets.Item(1) ' First tab countiung from the left
Dim rngA As Range: Set rngA = Ws1.Range("A1:A" & Ws1.UsedRange.Rows.Count & "")
Dim rngN As Range: Set rngN = Ws1.Range("N1:N" & Ws1.UsedRange.Rows.Count & "")
Dim arrA() As Variant, arrN() As Variant
Let arrA() = rngA.Value2: Let arrN() = rngN.Value2
Dim Nah As Long: Let Nah = Now
Dim Cnt As Long
For Cnt = 1 To Ws1.UsedRange.Rows.Count
If arrN(Cnt, 1) = "" And (Nah - arrA(Cnt, 1)) >= 150 Then rngA.Item(Cnt).Interior.Color = vbYellow
Next Cnt
End Sub
' msiyab
' Conditional Formatting of Dates (Multiple Criteria)
' Hi,
'
' I have an Cheque Book Register in Excel.
' Column A = Cheque Date
' Column N = Clearing Date
'
' I want the Conditional Formatting to highlight Column A if the below criterias are met.
' 1. Column N is blank.
' 2. If current date is 150+ days than the Column A date.
'
' I need this to know if a cheque is nearing its expiry date (6 months).
After:
_____ Workbook: CheckCheque.xlsm ( Using Excel 2007 32 bit )
| Row\Col |
A |
B |
C |
D |
E |
F |
G |
H |
I |
J |
K |
L |
M |
N |
O |
|---|
1 |
12.09.2018 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
2 |
13.09.2018 |
|
|
|
|
|
|
|
|
|
|
|
|
07.02.2019 |
|
3 |
12.09.2018 |
|
|
|
|
|
|
|
|
|
|
|
|
07.02.2019 |
|
4 |
11.09.2018 |
|
|
|
|
|
|
|
|
|
|
|
|
07.02.2019 |
|
5 |
10.09.2018 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
6 |
09.09.2018 |
|
|
|
|
|
|
|
|
|
|
|
|
07.02.2019 |
|
7 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Reply With Quote
Bookmarks