Conditional format - if A1 blank and A2 isn't

[jbiscoe]

Hi,

I'm building a spreadsheet for work that relies on a red, amber, green status to track due dates of letters that need to be sent. They all feed from one initial date in column A.

I'm using Excel 2003 and because of limited space on each PC in the department I havn't been able to use VBA code (big crashing problems), so am limited to only three conditional formats.

I have conditions to highlight when the due date is within 3 and 5 working days and then another once the 'date letter sent' cell is populated to clear the formatting, using =LEN(I2)>0

The formatting is copied down roughly 200 cells, is there a way to alter this rule to, roughly speaking, "if A2 is blank and/or I2 > 0" without inteferring with the other two conditions?

For the other two rules i'm using named areas: =H2<WorkDaythree and =H2>WorkDayfive

I apologise for the garbled description, I hope it makes sense to someone and thank you in advance for your help!

Regards

Jess

 

[moonfish]

You can do two things, I think:

Option 1)
Alter your current formulas to =AND(NOT(ISBLANK($A2)),current_formula)

Option 2)
Create a new conditional formatting rule, =ISBLANK($A2). Go to the'manage rules section of the conditional formatting menu, move this rule to the top and make sure the stop if true box is checked.

 

[jbiscoe]

Ah, thanks moonfish - option 1 worked a treat. Took me a minute to realise I had to wrap the code around all three conditions, but got there in the end.

Thanks

 

[Sharpedge]

Worked in my application too

Thanks