Complaints Sheet - Highlighting row if blank cell is blank for two days

**northern88** · 11-01-2012, 04:04 PM

Hi people. I need some help. I have created a complaints sheet for work. Basically this is how the columns work:

A - Date of Call
B - Time of Call
C - Name of Customer
D - Address
E - Message
F - Date Passed On
G - Days Taken to Resolve.

At the moment I have conditional formatting on columns A and F, if it is more than 2 days for the complaint to be addressed then the cell in column F turns red, less then 2 days and it turns green. Column G pretty much works the same I just have F minus A.

MAIN PROBLEM:

My boss wants the following to occur. If there is a date in column A but no date in column F/blank cell after 2 days. (i.e. the complaint hasn't been resolved) the entire row should highlight red. I'm at my wits end trying figure out how to get this working but with no results, anyone got any ideas?

SIDE PROBLEM:

There is a complaints sheet for each month, is it possible for the sheets tab to highlight red if that month has passed and there are any outstanding complaints not resolved?

**6StringJazzer** · 11-01-2012, 05:18 PM

Welcome to the Forum!

You would add a conditional formatting rule that would apply to A:G, using "use a formula"

=AND($F1="",$A1+2<TODAY())

and the format for the rule is red fill.

It is nearly always a good idea to attach a workbook; I would have implemented it for you

Your description, however, is very good.

As for your side problem, conditional formatting cannot apply to worksheet tab colors. However, it could be done with a macro if you want to go down that road.

**northern88** · 11-01-2012, 05:55 PM

Hey man thank you for the quick reply! I've implemented your formula and it works almost exactly how I need it to. One problem though, all the rows now are red and turn to no fill colour once a corresponding date has been entered, its sort of backwards the way it is at the moment (Red to no fill instead of no fill to red after stated time).

Could you post a correction on how to get around this or I have attached the spreadsheet to this post if you want to implement it.

**6StringJazzer** · 11-01-2012, 07:20 PM

Oh, sorry. I forgot to add a test for a blank row to the condition. I added $A1<>"" to the set of conditions. Also, since you also have the time of the call, I included that in the determination of elapsed time. Now the row turns red if more than 48 hours have passed since the date/time of the call. [northern88=conditional formatting based on elapsed time=Complaints Spreadsheet.xlsx]