Conditional Formatting of Dates (Multiple Criteria)

**msiyab** · 02-07-2019, 06:47 PM

**DocAElstein** · 02-07-2019, 09:01 PM

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	N
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

Worksheet: Tabelle1
_____ Workbook: CheckCheque.xlsm ( Using Excel 2007 32 bit )

_Row\^Col	A	N
1	=TODAY()-148
2	=TODAY()-147	=TODAY()
3	=TODAY()-148	=TODAY()
4	=TODAY()-149	=TODAY()
5	=TODAY()-150
6	=TODAY()-151	=TODAY()
7

Worksheet: Tabelle1

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	N
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

Worksheet: Tabelle1

**msiyab** · 02-10-2019, 09:45 AM

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?

**DocAElstein** · 02-10-2019, 02:32 PM

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

**msiyab** · 02-10-2019, 03:00 PM

Originally Posted by DocAElstein

Hi
_1 What is the error?

Run-time error '13':
Type mismatch

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.

**DocAElstein** · 02-10-2019, 07:25 PM

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.

**msiyab** · 02-11-2019, 12:08 PM

Cheque Book Register.xls

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.