Gray Out Expired Records

[Guest]

Dear experts,

I've a table with 5 columns. The first column is for "Date" (e.g. 15 Jun
07). Each row is a record. My question is, if the rows of records are not
belonged to current months, i.e. belonged to previous months, I want those
rows of records' text colour automatically changed to gray colour. Please
advise how to do this if it is possible.

Thanks in advance.

 

[Peo Sjoblom]

One way
Select the whole range, do format>conditional formatting
select formula is and type

=DATE(YEAR($A2),MONTH($A2),1)<DATE(YEAR(TODAY()),MONTH(TODAY()),1)

click the format button, select font and select a grey colour, click OK
twice

Replace A2 with the first cell with a date in your selected range, so if you
select from C4 to F100 and C4 is the active cell use C4


--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(Remove ^^ from email)

 

[T. Valko]

Try this:

Assume your data is in the range A1:E10
Select the range A1:E10
Goto the menu format>Conditional formatting
Formula Is: =AND(ISNUMBER($A1),MONTH($A1)<MONTH(TODAY()))
Click the Format button
Goto the Patterns tab
Select a shade of gray
OK out

 

[Guest]

Hi Biff,

Thanks for your help.

Try this:

Assume your data is in the range A1:E10
Select the range A1:E10
Goto the menu format>Conditional formatting
Formula Is: =AND(ISNUMBER($A1),MONTH($A1)<MONTH(TODAY()))
Click the Format button
Goto the Patterns tab
Select a shade of gray
OK out

 

[Guest]

Hi Peo,

Thanks for your help. Regards.

One way
Select the whole range, do format>conditional formatting
select formula is and type

=DATE(YEAR($A2),MONTH($A2),1)<DATE(YEAR(TODAY()),MONTH(TODAY()),1)

click the format button, select font and select a grey colour, click OK
twice

Replace A2 with the first cell with a date in your selected range, so if you
select from C4 to F100 and C4 is the active cell use C4


--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(Remove ^^ from email)

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

Hi Biff,

Thanks for your help.