Automatically Hightlight Rows with w/Date Check?

Using the latest version of Excel on WinXP. Have a spreadsheet which has a
date in one of the fields. What would be the best way to go about
automatically highlighting rows that had a date within the past 7 days? Any
help would be appreciated.

Hi Al,

Try conditional formatting, usung a formula like:

=AND($A1>TODAY()-7,$A1<TODAY()+1)
---
Regards,
Norman



"Al Franz" <[email protected]> wrote in message
news:[email protected]...
> Using the latest version of Excel on WinXP. Have a spreadsheet which has
> a date in one of the fields. What would be the best way to go about
> automatically highlighting rows that had a date within the past 7 days?
> Any help would be appreciated.
>

Use Conditional Formating.

This works automatically and doesn't require code.
The condition would have to apply to all cells in the row.

Select the total range. Change $A1 to the first cell in the
range.

Suggested formula might be (with date in column A)

Formula = "=ABS($A1-NOW())<8"
(remove quotes)

--
steveB

Remove "AYN" from email to respond
"Al Franz" <[email protected]> wrote in message
news:[email protected]...
> Using the latest version of Excel on WinXP. Have a spreadsheet which has
> a date in one of the fields. What would be the best way to go about
> automatically highlighting rows that had a date within the past 7 days?
> Any help would be appreciated.
>

Hi Steve,

The OP stipulated:

>> automatically highlighting rows that had a date within the past 7 days?


Your formula would appear to encompass a 16 day period which includes days
before and after today.


---
Regards,
Norman


"STEVE BELL" <[email protected]> wrote in message
news:p1RCe.12112$ph1.42@trnddc06...
> Use Conditional Formating.
>
> This works automatically and doesn't require code.
> The condition would have to apply to all cells in the row.
>
> Select the total range. Change $A1 to the first cell in the
> range.
>
> Suggested formula might be (with date in column A)
>
> Formula = "=ABS($A1-NOW())<8"
> (remove quotes)
>
> --
> steveB
>
> Remove "AYN" from email to respond
> "Al Franz" <[email protected]> wrote in message
> news:[email protected]...
>> Using the latest version of Excel on WinXP. Have a spreadsheet which has
>> a date in one of the fields. What would be the best way to go about
>> automatically highlighting rows that had a date within the past 7 days?
>> Any help would be appreciated.
>>
>
>

Norman,

My oops!...
should be...

Formula = "=NOW())-$A1<8"
or better
Formula = "=NOW())-$A1<7.0001"

--
steveB

Remove "AYN" from email to respond
"Norman Jones" <[email protected]> wrote in message
news:[email protected]...
> Hi Steve,
>
> The OP stipulated:
>
>>> automatically highlighting rows that had a date within the past 7 days?
>
>
> Your formula would appear to encompass a 16 day period which includes days
> before and after today.
>
>
> ---
> Regards,
> Norman
>
>
> "STEVE BELL" <[email protected]> wrote in message
> news:p1RCe.12112$ph1.42@trnddc06...
>> Use Conditional Formating.
>>
>> This works automatically and doesn't require code.
>> The condition would have to apply to all cells in the row.
>>
>> Select the total range. Change $A1 to the first cell in the
>> range.
>>
>> Suggested formula might be (with date in column A)
>>
>> Formula = "=ABS($A1-NOW())<8"
>> (remove quotes)
>>
>> --
>> steveB
>>
>> Remove "AYN" from email to respond
>> "Al Franz" <[email protected]> wrote in message
>> news:[email protected]...
>>> Using the latest version of Excel on WinXP. Have a spreadsheet which
>>> has a date in one of the fields. What would be the best way to go about
>>> automatically highlighting rows that had a date within the past 7 days?
>>> Any help would be appreciated.
>>>
>>
>>
>
>

Steve,

Thanks a lot, I took your advice and recorded a macro. The following is
what the code looked like. Is there an easy way to make the value of "8" I
coded in below a variable, so when I run the macro Excel would prompt me for
a value (i.e. number of days back to highlight)?

Cells.Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=NOW()-$P1<8"
With Selection.FormatConditions(1).Interior
.ColorIndex = 37
.Pattern = xlSolid
End With

add

Dim x As Integer

x = InputBox("enter number")

Cells.Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=NOW()-$P1<" & x
With Selection.FormatConditions(1).Interior
.ColorIndex = 37
.Pattern = xlSolid
End With


or
Dim x As Integer, rw As Long, col As Long

rw = Selection.Row
col = Selection.Column

x = WorksheetFunction.Count(Range(Cells(rw, 1), Cells(rw, col)))
--
steveB

Remove "AYN" from email to respond
"Al Franz" <[email protected]> wrote in message
news:eWFjXX%[email protected]...
> Steve,
>
> Thanks a lot, I took your advice and recorded a macro. The following is
> what the code looked like. Is there an easy way to make the value of "8"
> I coded in below a variable, so when I run the macro Excel would prompt me
> for a value (i.e. number of days back to highlight)?
>
> Cells.Select
> Selection.FormatConditions.Delete
> Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
> "=NOW()-$P1<8"
> With Selection.FormatConditions(1).Interior
> .ColorIndex = 37
> .Pattern = xlSolid
> End With
>
>
>