Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting
On my spread sheet I have rows of dates / 5 columns - what I am looking for
is a conditional format formula that I can apply to the entire row that would change the background color of any of the cells that the date is old then todays date. Thanks - George |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting
Use conditional formatting.
To spell it out. Let say B is the col you want formatted. Put formula =Today() in $A$1 Select Entire col B. Goto Menu - Format - Conditional Formatting Choose Cell Value is Less than $A$1 And presto.... |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting
Well you need to specify the different formatting you require in the
window that pops up. HTH, Jayant |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting
Is the date you want to check in column A?
1) Go to cell B1 (or wherever the first row of your data is, go into the 2nd column 2) Enter this as the conditional format condition Formula is: =AND(NOT(ISBLANK($A2)),$A2<TODAY()) be sure to use the $ before the column letter that points to your date 3) In conditional formatting set the color you want for the row and then click OK 4) Back on the sheet, now copy that cell 5) Highlight the entire row and do Paste Special... Paste Formats 6) Now copy this entire row 7) Highlight the rest of the rows (or even the whole worksheet, if desired) 8) Paste Special... Paste Formats. The relative/absolute referencing in the conditional format formula should take care of the referencing so it always checks whether the date in A for the current row is less than today's date. -- - K Dales "George" wrote: On my spread sheet I have rows of dates / 5 columns - what I am looking for is a conditional format formula that I can apply to the entire row that would change the background color of any of the cells that the date is old then todays date. Thanks - George |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting
K Dales,
How can I add the conditional formatting to meet the following criteria. Customers will input a date. Once the the date is exceed it changes colors. Also, can I have once a date is enter that it can't be changed. "K Dales" wrote: Is the date you want to check in column A? 1) Go to cell B1 (or wherever the first row of your data is, go into the 2nd column 2) Enter this as the conditional format condition Formula is: =AND(NOT(ISBLANK($A2)),$A2<TODAY()) be sure to use the $ before the column letter that points to your date 3) In conditional formatting set the color you want for the row and then click OK 4) Back on the sheet, now copy that cell 5) Highlight the entire row and do Paste Special... Paste Formats 6) Now copy this entire row 7) Highlight the rest of the rows (or even the whole worksheet, if desired) 8) Paste Special... Paste Formats. The relative/absolute referencing in the conditional format formula should take care of the referencing so it always checks whether the date in A for the current row is less than today's date. -- - K Dales "George" wrote: On my spread sheet I have rows of dates / 5 columns - what I am looking for is a conditional format formula that I can apply to the entire row that would change the background color of any of the cells that the date is old then todays date. Thanks - George |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting
Modify the formula supplied by K Dales creating a second condition
with a different color and use greater than when comparing date. You might want to use <= or = if you want to include dates equal\ with one set or the other. I have a page on Conditional Formatting http://www.mvps.org/dmcritchie/excel/condfmt.htm You will find a usage tutorial at http://www.datapigtechnologies.com/ExcelMain.htm and additional information at Bob Phillips, John McGimpsey, and Debra Dalgleish's respective sites there should be links on my condfmt.htm page. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "CurtH" wrote in message ... K Dales, How can I add the conditional formatting to meet the following criteria. Customers will input a date. Once the the date is exceed it changes colors. Also, can I have once a date is enter that it can't be changed. "K Dales" wrote: Is the date you want to check in column A? 1) Go to cell B1 (or wherever the first row of your data is, go into the 2nd column 2) Enter this as the conditional format condition Formula is: =AND(NOT(ISBLANK($A2)),$A2<TODAY()) be sure to use the $ before the column letter that points to your date 3) In conditional formatting set the color you want for the row and then click OK 4) Back on the sheet, now copy that cell 5) Highlight the entire row and do Paste Special... Paste Formats 6) Now copy this entire row 7) Highlight the rest of the rows (or even the whole worksheet, if desired) 8) Paste Special... Paste Formats. The relative/absolute referencing in the conditional format formula should take care of the referencing so it always checks whether the date in A for the current row is less than today's date. -- - K Dales "George" wrote: On my spread sheet I have rows of dates / 5 columns - what I am looking for is a conditional format formula that I can apply to the entire row that would change the background color of any of the cells that the date is old then todays date. Thanks - George |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I convert conditional formatting into explicit formatting? | Excel Discussion (Misc queries) | |||
Conditional formatting--different formatting depending on cell con | Excel Discussion (Misc queries) | |||
Formatting Conditional Formatting Icon Sets | Excel Discussion (Misc queries) | |||
Protect Cell Formatting including Conditional Formatting | Excel Discussion (Misc queries) | |||
Conditional Formatting that will display conditional data | Excel Worksheet Functions |