VBA Question on Date's in Excel

[Salvatore]

Is it possible to write a code that will look at a range of cells that have dates, and if the dates are 60 days old they will be highlighted like that in the conditional formating.

I am currently looking through some code I have that work with dates, I have not come across something like this...

If there are any ideas out there for me I would be appreciative..

Thank You..

[alex_read]

You could take a look at the DATEDIFF() function, though I've never used this myself.

This should do the trick though :

If CDate(Excel.Applicaton.Range("A1").value) > (Date() + 60) then ...

and I've even tested that one - 1st time this year

[Salvatore]

It is the first time for me as well...

I tried your suggestion, and nothing happens..

I don't know If I have to refer to the worksheet in the code.

As it is now this is what my code looks like. I am not getting any error when I run it, but the formating is not working.

Sub DateCheck ()
If CDate(Range("B7").Value) = (Date + 26) Then Range("p7").Value = "Yes"

End Sub

There has to be something I am forgetting?????

[DJDANNYK]

Try this one

Sub CheckDate()
    Dim CellDate As Date
    CellDate = CDate(Range("A1").Value)
    If Date - CellDate > 60 Then Range("A1").Font.Bold = True
End Sub

[Salvatore]

That worked for the one cell range. What I need is for that to scan a range of cells such as :

Dim CellDate As Date
CellDate = CDate(Range("B7:B9").Value)
If Date - CellDate > 10 Then Range("B7:B9").Font.Bold = True

When I run this code I get a Type Mismatch Error!!

Also when i try to change the format of the cell to highlight in yelllow when the logic statement is true, I get an error message say that it can not change the Color INdex.

Any guesses as to how to solve these two problems??

Thanks for the help so far...

[DJDANNYK]

If you have multiple cells then you would need to create a loop around these cells to check each one individually..

Lets try this instead

Sub DateCheck()
    Dim CellDates(), i As Integer, j As Integer
    'Assuming Cell A1 is the start of this range of dates and we do not know 
    'the last cell but the cell after the last cell is empty
    Range("A1").Select
    While Activecell.Value <> ""
        i = i + 1
        Activecell.Offset(1,0).Select
    Wend
    'Now redim the CellDates Array so we can populate it with all values
    ReDim CellDates(1 to i)
    'place each cell into the array
    Range("A1").Select
    For j = 1 to i
        CellDates(j) = "A" & j
    Next j
    'Now loop through this array checking the values of the cell and changing the font to bold
    'and the cell background to yellow
    For j = 1 to i
        If Date - Cdate(Range(Celldates(j)).Value) > 60 then
            Range(CellDates(j)).Font.Bold = True
            Range(CellDates(j)).Select
            With Selection.Interior
                .ColorIndex = 6
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
            End With
        End If
    Next j
End Sub

That should do the trick

[Salvatore]

It looks like this is going to work. the only thing is when I tried the code, I received a Type-mismatch error on this line of code:

If Date - CDate(Range(CellDates(j)).Value) > 60 Then

My first cell starts on B7, so I inputted that instead of A1.

An idea why the error??

[DJDANNYK]

The value in the cell is invalid... put the code in debug mode and step through until you reach the error..

What is the cell's value??

[Salvatore]

The cell value is 'DATE'....

When I returned to the worksheet after compiling the code, I noticed that cells A1:A5 were higlighted in yellow with a bold font.

I thought that if I changed the code to read the first cell as B7 that it would loop from there????

[Salvatore]

The cell value that i gave was referring to the Column heading. It was reading from cell B6 which has the value DATE.

[Static]

try a for..next loop with Cells() instead
A1 is 1,1
B7 is 7,2
Cells(row,column)

Sub CheckDate()
Dim CellDate As Date
for x = 7 to 9
CellDate = CDate(Cells(x,2).Value)
If Date - CellDate > 60 Then cells(2,x).Font.Bold = True
End Sub

[DJDANNYK]

Not unless you change this aswell

For j = 1 to i
        CellDates(j) = "A" & j
    Next j

to this

For j = 1 to i
        CellDates(j) = "B" & j + 6
    Next j

If you are picking up a value in a cell that does not equal a date value then you will need to error trap like so

For j = 1 to i
        On Error Goto InvalDate
        If Date - Cdate(Range(Celldates(j)).Value) > 60 then
            Range(CellDates(j)).Font.Bold = True
            Range(CellDates(j)).Select
            With Selection.Interior
                .ColorIndex = 6
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
            End With
        End If
        On Error Goto 0
    Next j
    Exit Sub
 
InvalDate:
    msgbox Err.Description
    Exit Sub
End Sub

I've just tried my code on a full column of random dates and and it work no problems

[Salvatore]

Now I am receiving a Subscript Out of Range error. When I run the debugger it brings up this line of code in yellow:

ReDim CellDates(1 To i)

[DJDANNYK]

I'll try a re-create your spreadsheet and see what I can change in the code for you

[DJDANNYK]

Hmmm..

I changed my Code to this

Sub DateCheck()
    Dim CellDates(), i As Integer, j As Integer
    'Assuming Cell A1 is the start of this range of dates and we do not know
    'the last cell but the cell after the last cell is empty
    Range("B7").Select
    While ActiveCell.Value <> ""
        i = i + 1
        ActiveCell.Offset(1, 0).Select
    Wend
    'Now redim the CellDates Array so we can populate it with all values
    ReDim CellDates(1 To i)
    'place each cell into the array
    Range("B7").Select
    For j = 1 To i
        CellDates(j) = "B" & j + 6
    Next j
    'Now loop through this array checking the values of the cell and changing the font to bold
    'and the cell background to yellow
    For j = 1 To i
        If Date - CDate(Range(CellDates(j)).Value) > 60 Then
            Range(CellDates(j)).Font.Bold = True
            Range(CellDates(j)).Select
            With Selection.Interior
                .ColorIndex = 6
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
            End With
        End If
    Next j
End Sub

And it worked.. the spreadsheet that I am using has only 4 populated cells B6 to B9 with B6 Containing "DATE" and the remaining three cells contain the dates 1/1/01, 31/3/01, 1/6/01 respectively

[Salvatore]

Thank You DJDANNYK...your code did work, i forgot to chane the range address to B7.

You have helped me immensely!!!!!!!!!

I have also learned alot from you in this experience....

Keep up the great work!!!!!!!!!!

[DJDANNYK]

No problem..

I prefer to put extra stuff into the code examples to help newbies learn additional stuff that they might not of thought of..

Come back to me if you have any other questions with VBA for Excel... been using VBA for Excel for 3 years now, and I'm considered THE Guru around my workplace