highlighted specific dates based on today's date

[nackers123]

I have a list of dates in column A and need to identify (probably by conditional formatting?) which dates occur between the last but one 21st of the month and the last 20th. Thus if today's date is the 21st September, entries in the list between 21st August and 20th September should be highlighted. If today's date is the 20th September then entries between 21st July and 20th August should be highlighted. Hope someone can help?

 

[s_simon]

I have a list of dates in column A and need to identify (probably by conditional formatting?) which dates occur between the last but one 21st of the month and the last 20th. Thus if today's date is the 21st September, entries in the list between 21st August and 20th September should be highlighted. If today's date is the 20th September then entries between 21st July and 20th August should be highlighted. Hope someone can help?

Try something along the below
Dim BackOneMonth As String
Dim BackTwoMonth As String
BackOneMonth = Month(Date) - 1
BackTwoMonth = Month(Date) - 2
Dim One As Date
Dim Two As Date
One = BackOneMonth & "/20/" & Year(Date)
Two = BackTwoMonth & "/21/" & Year(Date)
Dim rowcount As Integer
rowcount = Worksheets("Sheet1").Range("A" & Sheet1.Rows.Count).End(xlUp).Row
Dim p As Long, j As Long
j = 2
For p = 2 To rowcount
If Cells(p, "A").Value >= Two And Cells(p, "I").Value <= One Then
Cells(p, "A").Interior.ColorIndex = 46
j = j + 1
End If
Next p

Without really digging in - here are a few issues (but the above should get you going) - problem area 1) the dates above ONE are still being colored and 2) the Year will be an issue in Jan and Feb

 

[nackers123]

Thanks s_simon
My first attempt at VB, appears to highlight every date! Any ideas on what I am doing wrong?

Try something along the below
Dim BackOneMonth As String
Dim BackTwoMonth As String
BackOneMonth = Month(Date) - 1
BackTwoMonth = Month(Date) - 2
Dim One As Date
Dim Two As Date
One = BackOneMonth & "/20/" & Year(Date)
Two = BackTwoMonth & "/21/" & Year(Date)
Dim rowcount As Integer
rowcount = Worksheets("Sheet1").Range("A" & Sheet1.Rows.Count).End(xlUp).Row
Dim p As Long, j As Long
j = 2
For p = 2 To rowcount
If Cells(p, "A").Value >= Two And Cells(p, "I").Value <= One Then
Cells(p, "A").Interior.ColorIndex = 46
j = j + 1
End If
Next p

Without really digging in - here are a few issues (but the above should get you going) - problem area 1) the dates above ONE are still being colored and 2) the Year will be an issue in Jan and Feb

 

[Fluff]

With Cf formula

=AND(A1>=DATE(YEAR(TODAY()),MONTH(EDATE(TODAY(),IF(DAY(TODAY())>20,-1,-2))),21),A1<EDATE(DATE(YEAR(TODAY()),MONTH(EDATE(TODAY(),IF(DAY(TODAY())>20,-1,-2))),21),1))

 

[Tetra201]

Here is a shorter CF rule:

=AND(A1>=EOMONTH(TODAY()-20,-2)+21,A1 < EOMONTH(TODAY()-20,-1)+21)<eomonth(today()-20,-1)+21)< html=""></eomonth(today()-20,-1)+21)<>