Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Check if date is between two dates, then sum only those rows | Excel Worksheet Functions | |||
formula to check the date sequence in rows | Excel Discussion (Misc queries) | |||
Hightlight date range | New Users to Excel | |||
format column to automatically move rows to date order | Excel Worksheet Functions | |||
... Can I set Spell Check to automatically check my spelling ... | Setting up and Configuration of Excel |