Applying Conditional Formatting Formula Across a Range of Cells

I am having trouble applying a conditional formatting rule to a range of cells based on the value in corresponding cells in another column.

Currently in Column H I have a formula to determine a "due date timer" using Due Date (Column G)- today [ex. =g1-today()], based upon the value returned form this formula I have conditional formatting setup that <=5 days and <=10 days as well as blank cells (see attached image)

What I am trying to do is once a date is entered into Completion Date (Column L) I would like the red or yellow coloring to stop. I have successfully completed this for a single cell that H7 will turn back to white if L7 has a date entered but I need this same formatting for each corresponding cell. When I attempted to complete this initially I was causing all Due Date Timers to change even if the date value was not in the corresponding Completion Date cell, if a date was entered anywhere in that column it was changing the format to all Due Date cells. Is it possible to write a single rule for a range of cells based upon the corresponding cell in another range of cells? Or do I need to complete these rules individually for each cell (i.e. H7 to L7, H8 to L8, H9 to L9)?


formulas.jpg

Hello welcome to the forum.

Select your range of cells in Column H and apply the following CF formula for yellow:

Formula:

Please Login or Register  to view this content.

and for red:

Formula:

Please Login or Register  to view this content.

When a numeric value (date) is entered in adjacent column L then cells turn yellow or red depending on criteria. Empty has no fill as does anything over 10.

DBY

I appear to not be getting the expected results. The colors seem to be applying themselves to fields without data and those that previously had coloring now do not. If there is no date in the Completion Date column the colors would need to remain. Am I applying the formulas incorrectly to the range of data I need analyzing to create the CF? Please see below

formula 2.jpg

Hi
It's difficult to see what you're doing from those screenshots but I've attached an example file showing how I'd interpreted your problem. The formatting is only applied to the 'Due Date Timer' field so shouldn't affect any other columns.

DBY

Thanks DBY.

I think there is one element missing so let me see if I can explain this a little better.

A worker enters in the due date (column G,) from there the Due Date timer uses the formula of =G-today() to create a count of the number of days until the item is due. (So from my example, due date 9/9/16, when I posted the image on 9/10/16 had 1 day left before it was due.)

I would like conditional formatting on the Due Date Timer (column f) to change as due dates come near, so yellow for =<10 days or red for =<5 days so the worker can review this log and easily identify time sensitive items.

The added element to this is however, if the item has been worked and a completion date is input, I would like the conditional formatting of red or yellow to stop on the due date time as this no longer matters, the item is complete.

I'm able to create the conditional formatting for the Due Date Timer to get the red and yellow however I am having difficulty finding a way to stop the CF based upon an entry into the completion date without creating rules for each and every row as opposed to some blanket rules that would cover a range of rows.

Any help is appreciated

It's becoming a little clearer now. See the amended example. Does this do as required?

DBY

thanks! This is perfect!