Conditional Formatting

[Guest]

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

 

[jjk]

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....

 

[jjk]

Well you need to specify the different formatting you require in the
window that pops up.

HTH,
Jayant

 

[Guest]

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.

 

[Guest]

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.

 

[David McRitchie]

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.