How Do I Write a Macro for Highlighting Rows Based on Date Criteria

I need my spreadsheet to perform the following:

-If today's date is later than the date in column E and there are no dates in columns F-J, Highlight the row orange until a date is filled in to either of those columns.

-If today's date is two or more days later than the date in column F and there is no date in column G-J, highlight the row light blue until a date is filled in to either of those columns.

-If today's date is later than the date in column G and there is no date in column H or J, highlight the row yellow until a date is filed in to column H or J.

I was able to figure out how to perform the first bullet using conditional formatting using the following:
=IF(ISBLANK($E157),"",(IF($E157<TODAY(), (AND(ISBLANK(F157),ISBLANK(G157),ISBLANK(H157),ISBLANK(I),ISBLANK(J157),(IF(ISBLANK($F157),"",(IF($F157<=TODAY()+2, (AND(ISBLANK(G157),ISBLANK(H157),ISBLANK(I157),ISBLANK(J157))))))))))))

I couldn't get any of the other rules to work and was told that I may have to use Macros. I have no experience creating Macros and have tried multiple avenues for help on the internet and within Excel.

I am hoping that someone will be able to help me in this forum.

Thanks in advance
Tara

Thank you for your help. I'll try this to see if I can get it to work. I also did not mean to enter 2003. It should be 2007. How big of a difference will that make?

Originally Posted by JBeaucaire

In 2003 you only get 3 CF formulas in a single cell. That limitation doesn't exist in 2007+, you can add as you need.

If that takes care of your original query, please select Thread Tools from the menu above and mark the thread as solved. Thanks.

That worked. Thank you so much for your help. I am so glad that it did not require macros as I was told.

I appreciate the time you took to help me with this!

Tara

Originally Posted by JBeaucaire

Most CF formulas don't need to be IF tests, you simply test the criteria and without the IF the result will return TRUE/FALSE by design. To understand this, put this formulae in some cells:

=1=1 (should show TRUE)
=2>3 (should show FALSE)

Now just do that same thing with your more complex tests.


RED Rule #1:
=AND(TODAY()>$E157, $E157>0, COUNT($F157,$J157)=0)

BLUE Rule #2:
=AND(TODAY()-$F157>1, $F157>0, COUNT($G157,$J157)=0)

YELLOW Rule #3
=AND(TODAY()-$G157>1, $G157>0, COUNT($H157,$J157)=0)


NOTE: That's 3 CF rules, that's all you get in one cell in Excel 2003. You should be upgrading soon, yes?

JBeaucaire,

Once I applied the rules to my full spreadsheet they no longer worked properly. There are rows highlighted that should not be, and ones that won't unhighlight if you add a date into one of the later columns.

I'm not sure why it seemed to work so well when I just applied it to the last 10 rows of my spreadsheet.

What could I be doing wrong? I have attached my spreadsheet.

Thank you for your help.

Originally Posted by JBeaucaire

Example rows you want checked?

Examples:
Row 25 is Yellow and should be Blue.

Rows 27,41,46,51 are examples of rows that are still orange (RULE #1 says RED), but should be blank since there are dates in the later columns.

Row 28 Should be Yellow, but it's blank

Rows 36,66,70 are examples of rows that should be Orange

Row 73 should no longer be Blue



I am at a loss as to why it worked when my cell range was A157:J168. I used just a small section so I wouldn't mess up my spreadsheet while I played around with getting it to work.

I'll have to go back and see why it works on the smaller parameter. Was thinking about opening a new sheet, entering in the CF's and my date formula. Then, copy and paste in the data and see if it works that way. I haven't done anything different from when it was working on my smaller range of rows

I'll get back on it tomorrow. Too frustrated with it :-)

Well it looks like it was an issue with the different versions of Excel and what version it was saved in. When simply saved as an excel workbook, it was fine. If you try to save it as a specific version of excel there is an issue with the formatting, and it no longer works properly.

I changed one thing with the conditional formatting rules that you provided which made it work as needed:

=AND(TODAY()>$E2,$E2>0,COUNT($F2:$J2)=0)

Adding in the colon instead of a comma in the COUNT statement made the highlight function as expected when data was entered into later columns.

The spreadsheet is up and running perfectly now. I thank you for your help on this.

Tara