date comparison

[Matthew Dyer]

My range of data has date values in column H (row 1 is header) for the
last time a file was worked. it is in DD-MMM-YY format (ex 23-NOV-09).
What I'd like to do is run a macro that will bold a row if the date in
column H is 5 days old or older. I have no idea how to do this with
dates... I tried to re-format column H into number "0" format and use
Now to get today's day and change that into a "0" format as well to do
my comparison but that failed as well. Help... please?

 

[Rick Rothstein]

Select your cells from Row 2 down as far as will be needed and use this
formula in the Format Condition...

=H2+5<=TODAY()

 

[Matthew Dyer]

Select your cells from Row 2 down as far as will be needed and use this
formula in the Format Condition...

=H2+5<=TODAY()

--
Rick (MVP - Excel)






- Show quoted text -

I would very much appreciate it if someone could help with vba coding
to help achieve this end, but I am willing to try your suggestion,
however... I'm using excel 2002. I can find the conditional formatting
part, but I cant find the "format condition" field to enter a formula
like you've provided.

 

[Matthew Dyer]

I would very much appreciate it if someone could help with vba coding
to help achieve this end, but I am willing to try your suggestion,
however... I'm using excel 2002. I can find the conditional formatting
part, but I cant find the "format condition" field to enter a formula
like you've provided.- Hide quoted text -

- Show quoted text -

Ok, I found where I can enter formulas for the conditional formatting.
Could someone help me with using dates in VBA coding?

 

[Rick Rothstein]

I don't have XL2002, rather I have XL2003, but the procedure should be the
same. Click the first drop in the dialog box and select "Formula Is" from
the list. The stuff to the right of the drop down will change to a single
text field... this is where you place the formula. HOWEVER, I just noticed
that you want the whole row formatted, not just the cells in the column...
the formula I gave you won't do that, you would have to apply the format to
all the cells in the range and that might not be all that efficient. Let's
try it with a macro solution that you indicated you wanted instead...

Sub MakeOldDatesBold()
Dim X As Long, LastRow As Long
LastRow = ActiveSheet.Cells(Rows.Count, "H").End(xlUp).Row
For X = 2 To LastRow
Rows(X).Characters.Font.Bold = Cells(X, "H").Value + 5 <= Date
Next
End Sub

--
Rick (MVP - Excel)

Select your cells from Row 2 down as far as will be needed and use this
formula in the Format Condition...

=H2+5<=TODAY()

--
Rick (MVP - Excel)






- Show quoted text -

I would very much appreciate it if someone could help with vba coding
to help achieve this end, but I am willing to try your suggestion,
however... I'm using excel 2002. I can find the conditional formatting
part, but I cant find the "format condition" field to enter a formula
like you've provided.

 

[Paul C]

The conditional format is the best way.

If you really want a macro this will work

Sub Boldme()
lastrow = Range("h10000").End(xlUp).Row
For A = 2 To lastrow
If Cells(A, 8) <= Date - 5 Then
Cells(A, 8).Font.Bold = True
End If
Next A

End Sub

 

[Rick Rothstein]

IF you still want to go the Conditional Format route, select all the cells
from A2 (note I said A2, not H2) to the last column you want to highlight
and down to the last row you would ever have data in, and use this
formula...

=AND($H2+5<=TODAY(),$H2<>"")

Make sure you press the Format button and select Bold from the Font tab in
order to set the format you want.

--
Rick (MVP - Excel)

I would very much appreciate it if someone could help with vba coding
to help achieve this end, but I am willing to try your suggestion,
however... I'm using excel 2002. I can find the conditional formatting
part, but I cant find the "format condition" field to enter a formula
like you've provided.- Hide quoted text -

- Show quoted text -

Ok, I found where I can enter formulas for the conditional formatting.
Could someone help me with using dates in VBA coding?

 

[Matthew Dyer]

I don't have XL2002, rather I have XL2003, but the procedure should be the
same. Click the first drop in the dialog box and select "Formula Is" from
the list. The stuff to the right of the drop down will change to a single
text field... this is where you place the formula. HOWEVER, I just noticed
that you want the whole row formatted, not just the cells in the column....
the formula I gave you won't do that, you would have to apply the format to
all the cells in the range and that might not be all that efficient. Let's
try it with a macro solution that you indicated you wanted instead...

Sub MakeOldDatesBold()
  Dim X As Long, LastRow As Long
  LastRow = ActiveSheet.Cells(Rows.Count, "H").End(xlUp).Row
  For X = 2 To LastRow
    Rows(X).Characters.Font.Bold = Cells(X, "H").Value + 5 <= Date
  Next
End Sub

--
Rick (MVP - Excel)






I would very much appreciate it if someone could help with vba coding
to help achieve this end, but I am willing to try your suggestion,
however... I'm using excel 2002. I can find the conditional formatting
part, but I cant find the "format condition" field to enter a formula
like you've provided.- Hide quoted text -

- Show quoted text -

Thanks for all your help! this works perfectly. I was trying to use
Now instead of Date. I guess that would be a problem wouldn't it?

 

[Rick Rothstein]

You can replace Now for Date in my code and the macro will perform
correctly; however, since you are not testing against time values, there is
no benefit to using the Now function.

--
Rick (MVP - Excel)

I don't have XL2002, rather I have XL2003, but the procedure should be the
same. Click the first drop in the dialog box and select "Formula Is" from
the list. The stuff to the right of the drop down will change to a single
text field... this is where you place the formula. HOWEVER, I just noticed
that you want the whole row formatted, not just the cells in the column...
the formula I gave you won't do that, you would have to apply the format
to
all the cells in the range and that might not be all that efficient. Let's
try it with a macro solution that you indicated you wanted instead...

Sub MakeOldDatesBold()
Dim X As Long, LastRow As Long
LastRow = ActiveSheet.Cells(Rows.Count, "H").End(xlUp).Row
For X = 2 To LastRow
Rows(X).Characters.Font.Bold = Cells(X, "H").Value + 5 <= Date
Next
End Sub

--
Rick (MVP - Excel)






I would very much appreciate it if someone could help with vba coding
to help achieve this end, but I am willing to try your suggestion,
however... I'm using excel 2002. I can find the conditional formatting
part, but I cant find the "format condition" field to enter a formula
like you've provided.- Hide quoted text -

- Show quoted text -

Thanks for all your help! this works perfectly. I was trying to use
Now instead of Date. I guess that would be a problem wouldn't it?