Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 21
Default 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.


  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,302
Default 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.



  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 692
Default 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.



  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,302
Default 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.





  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 692
Default 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.









  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 21
Default 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



  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 692
Default 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





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Check if date is between two dates, then sum only those rows streetcar Excel Worksheet Functions 2 February 16th 10 01:19 PM
formula to check the date sequence in rows brenda Excel Discussion (Misc queries) 4 July 2nd 09 04:34 AM
Hightlight date range Derek New Users to Excel 2 June 3rd 09 01:03 AM
format column to automatically move rows to date order Mark Kruger Excel Worksheet Functions 1 February 18th 08 10:53 PM
... Can I set Spell Check to automatically check my spelling ... Dr. Darrell Setting up and Configuration of Excel 0 March 21st 06 09:26 PM


All times are GMT +1. The time now is 04:36 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"