Conditional Formatting - colour cell depending on date in different call

[Aconway]

I would like column A (for each row) to change to red when the value of columns E through to Z (in each row) are any date older than today. Also I'd like column A to turn orange if anything in E-Z are coming up within 30 days...if possible. I've looked up many conditional formatting guides and just can't figure it out. Please help!

[63falcondude]

Hello and welcome to the forum.

I recommend uploading a small representative sample of your data along with the desired results (which you can enter manually) based on that data.

To upload an Excel workbook, follow these steps:
1) Click on "Go Advanced"
2) Click on "Manage Attachments"
3) Click on "Choose File"
4) Choose your file and click on "Open"
5) Click on "Upload"
6) Click on "Close this window"

[63falcondude]

I'm assuming that you want column A to turn red if any date in E:Z are older than today.
Similar for orange.

Try this:

Highlight A2:A100 (or however far your data goes) > Conditional Formatting > New Rule > Use a formula
=COUNTIF(E2:Z2,"<"&TODAY()+30)
Format: Fill orange > OK > OK

Highlight A2:A100 (or however far your data goes) > Conditional Formatting > New Rule > Use a formula
=COUNTIF(E2:Z2,"<"&TODAY())
Format: Fill red > OK > OK

[Aconway]

I'm assuming that you want column A to turn red if any date in E:Z are older than today.
Similar for orange.

Try this:

Highlight A2:A100 (or however far your data goes) > Conditional Formatting > New Rule > Use a formula
=COUNTIF(E2:Z2,"<"&TODAY()+30)
Format: Fill orange > OK > OK

Highlight A2:A100 (or however far your data goes) > Conditional Formatting > New Rule > Use a formula
=COUNTIF(E2:Z2,"<"&TODAY())
Format: Fill red > OK > OK

Hi there

Thankyou so much, this worked! How would I place this formula to the whole of column A instead of doing so individually? Would it be $A$2:$A$65 for example?

[JeteMc]

Yes, if you want the rules to apply to column A, rows 2 through 65, place =$A$2:$A$65 in the applies to window adjacent to the rules for both orange and red.
Let us know if you have any questions.

[johnp19922011]

I have a similar question. How can I do this with a monetary value instead of a date.

For instance turn text in A1 red if the the value in A2 is a negative value otherwise stay black.

[63falcondude]

You really should be creating your own thread. You could link to this one if you felt it was necessary.

That being said, this will work according to your description:

Highlight A1 > Conditional Formatting > New Rule > Use a formula
=A2<0
Format: Fill red > OK > OK