Conditional Formatting based on 2 Criteria.

[Philip1957]

Greetings,

I'm sure that when I get this figured out it will be one of those "duh" moments, but none-the-less here's my question.

I want to apply conditional formatting to ($X3) only if cell ($Y3) is blank and the date in ($X3) is older than today.

I've tried variations of =AND($X3<TODAY(), ISBLANK($Y3)) applied to the X column and I can get the cell to format based on the date criteria but not the condition of Y being blank. I need both conditions to be True for the formatting to be applied.

Thanks in advance,
~ Phil

 

[Joe4]

OK, so it sounds like maybe you are not applying the Conditional Formatting rule to all rows properly.
Follow these steps.
1. Remove ALL current Conditonal Formatting
2. Highlight the whole range you want to apply this to (So if just column X, highlight X3 down to the last row in X you want to apply this to. If you want the whole row, highlight row 3 down to the last row you want to apply it to).
3. Write the Conditional Formatting formulas as it applies to the first cell in your highlighted range. The use of Absolute/Relative range references is critical in getting this to work properly.
So write the formula EXACTLY like this:

[COLOR=#333333]=AND($X3 < TODAY(),$Y3="")[/COLOR][COLOR=#333333]

[/COLOR]
<today(),y$3="")< font="">4. Pick your formatting option
5. Click OK

This should work. Do not be confused if you look at a cell in the middle of your range and the formula looks a little weird (like it is is referencing the wrong cell). No matter what cell you look at, it is going to show your the formula for the first cell in the range.</today(),y$3="")<>

 

[Philip1957]

Solved!

I knew it would be a "duh" mistake.

Row 1 is merged cells. If I un-merge, apply AND(X1 < TODAY(), ISBLANK(Y1)), and then re-merge, everything works just fine.

Thanks again for everyone's help.

~ Phil

 

[Joe4]

You should really try to avoid using merged cells whenever possible. They are notorious for wreaking havoc in Excel with many things like sorting, filtering, VBA, etc.
The good news is you can get the same visual effect you would get from merging cells across a row without all the problems by using the Center Across Selection formatting option instead.

See here: http://blog.contextures.com/archives/2014/04/10/center-headings-without-merging-cells/

 

[Philip1957]

Joe4,

Yes I know.

Unfortunately my Manager, who knows nothing about VBA, can be rather insistent about the appearance of a spreadsheet and he likes merged cells justified to the left.

The man drives me nuts. However, I have found out that in my VBA I can frequently get around the merged cell problem in VBA by un-merging, performing actions and then re-merging at the end of the Sub. Inefficient as heck I know, but it works (most of the time).



~ Phil

 

[Joe4]

he likes merged cells justified to the left.

What exactly is in the cells? Text or Numbers?
If Text, how it is any different than just left justifying the cell. It should "spill over" by default anyway.