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.

Automatically Hightlight Rows with w/Date Check?
 

Hi Al,

Try conditional formatting, usung a formula like:

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



"Al Franz" wrote in message
...
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.

Automatically Hightlight Rows with w/Date Check?
 

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" wrote in message
...
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.

Automatically Hightlight Rows with w/Date Check?
 

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" 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" wrote in message
...
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.

Automatically Hightlight Rows with w/Date Check?
 

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" wrote in message
...
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" 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" wrote in message
...
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.

Automatically Hightlight Rows with w/Date Check?
 

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

Automatically Hightlight Rows with w/Date Check?
 

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" wrote in message
...
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