Flagging a cell

[Oscar]

Hi,

I have two cells that I would like to use to flag one. I have a cell that
would show if an appraisal was done inside a home or outside. My Cell title
is "Apprsl I or E" and I have a list of properties that have an appraisal of
I or E. The column next to it is the appraisal date. I would like to flag the
appraisal date column cells if appraisal I is older than 6 months from
todays date and if appraisal E is older than 9 months from todays date.

Thanks

 

[Shane Devenshire]

Hi,

To conditionally format your cell(s):

In 2003:
1. Select the cells you want to format
2. Choose Format, Conditional Formatting
3. Choose Formula is from the first drop down
4. In the second box enter the formula:
=AND(B2="I",C2<DATE(YEAR(NOW()),MONTH(NOW())-6,DAY(NOW())))
4a. Click the Format button and choose a color on the Patterns tab
4b. Click OK once, then click Add and enter the following second conditon:
=AND(B2="E",C2<DATE(YEAR(NOW()),MONTH(NOW())-9,DAY(NOW())))
5. Click the Format button
6. Choose a color on the Patterns tab (or any available option)
7. Click OK twice.

In 2007:
1. Highlight all the cells on the rows you want formatted
2. Choose Home, Conditional Formatting, New Rule
3. Choose Use a formula to determine which cell to format
4. In the Format values where this formula is true enter the following
formula:
=AND(B2="I",C2<EDATE(TODAY(),-6))
5. Click the Format button and choose a format.
6. Click OK twice
7. Repeat the steps to add a second condition
=AND(B2="E",C2<EDATE(TODAY(),-9))

If this helps, please click the Yes button.

Cheers,
Shane Devenshire

 

[Oscar]

What if I want to repeat this condition to the next cell (C3)?

Thanks

 

[Shane Devenshire]

Hi,

assuming that in the next cell you want to compare the entries on row 3 then
simply copy the format down. Or as step one suggested select all the cells
you want to format before you create the conditonal formatting. But note
that this formula assumes the first cell of that selection is on row 2.
Adjust if that is not the case.

You can copy formats using either the Format Painter or the copy, Edit,
Paste Special, Format command.