-
Conditional Formatting of Dates (Multiple Criteria)
-
1 Attachment(s)
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
Quote:
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 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
-
Hi,
Just checked the VBA, and I'm getting an error for this line:
If arrN(Cnt, 1) = "" And (Nah - arrA(Cnt, 1)) >= 150 Then
What might be the reason?
-
Hi
_1 What is the error?
_2 can you upload a small amount of test data where you have the error , ( Change any personal or senstive data )
Alan
-
Quote:
Originally Posted by
DocAElstein
Hi
_1 What is the error?
Run-time error '13':
Type mismatch
Quote:
Originally Posted by
DocAElstein
_2 can you upload a small amount of test data where you have the error , ( Change any personal or senstive data )
Alan
Sorry its a company worksheet, with multiple formula, links, etc.
-
Hi
I expected that it might be that type of error. Such an error is typical when tying to compare dates.
I cannot help further without some test data.
I only need column A and column N.
Can you upload a small sample with just column A and column N. You can delete all other information. I only need to see the two columns of dates.
-
1 Attachment(s)
Attachment 2156
I have attached a sample workbook
Please note, I have added a few more columns. So the previous Column N is now Column L in the new file.
-
5 Attachment(s)
Hi msiyab
Thanks for sample workbook
To find Where is problem:
We can look at variables…
But first
-
1 Attachment(s)
Attachment 2162
Still does not seem to work. The highlight remains no matter which date i put.
Am i doing something wrong here?
-
Explain again very carefully what you want.
Give examples to show all scenarios
What is the current problem ?
