Conditional Formatting for entire row based on ranges of number in column F

[DCnyc]

Hi,

Hope someone can help me here....

I've attached a small excel file where I need some conditional formatting to work, but I'm stumped. Pretty sure I would have to use the "Use formula to determine which cells to format", but don't know which formula to use.

I need rows to be specific colors (shown in the attachment) as related to certain numbers.

So, in column F - Days Until Due/Overdue, if the negative numbers (2) through (6) appear (these cells reference the Due Date column D2 using the Networkdays formula) then I want all of those cell rows to be yellow, text is red. For negative numbers (7) through (11), I want the cell rows to be orange, for negative numbers (12) through (16) to be orange, accent 6, darker 25%, text to be black for visibility and for numbers that are (17) AND ABOVE, then those rows would all be red, text to be black for visibility. These items would be all OVERDUE.

For the number 1 generated, then that row should be green.

For positive numbers greater than 1, then those are all gray, Days Until Due.

I'm showing these numbers in a numerical order for explanation purposes only, that's not really how they show up based on Due Date when I run the report.

These numbers will change on a daily basis when someone opens the file, again based on the Networkdays formula, so I would expect the colors to change accordingly, that's why I figured conditional formatting would work.

[6StringJazzer]

You need a rule for each color. You are correct that you need a formula, because you want to color multiple cells based on the content in column F. See Solution sheet attached.

Example:
Yellow:

=AND(-6<=$F2, $F2<=-2)

You do not need to specify font formatting in the conditional formatting rules because it's already built into the number formatting you are using.

[DCnyc]

Jeff - Thank you so much for your help!

[6StringJazzer]

You're welcome!

If your question has been answered please mark your thread as "Solved" so that members will know by looking at the thread title that your problem is solved. Go to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..."

If a member helped you solve your problem, consider adding to their reputation by clicking addreputationiconsmall.jpg below their name.

[DCnyc]

I have another issue with conditional formatting in my excel report. I realized after posting on this thread and getting a response that solved the report to, I now realize, a point.

There are two conditions I need to meet that involves Column F, Days Until Due and Column E, Ball In Court...

Where John Smith's name shows up in Column E, whether singly or with other names present (i.e. cell rows 2, 3, 4, 6) and the Column F numbers are 1 or greater, the whole cell row needs to be green.

Where names other than John Smith's appear and the Column F numbers are 1 or greater, the whole cell needs to have no fill.

I've been pulling my hair out trying to configure the formula using AND, IF, THEN, etc. and could use some help here.

FYI - The conditional formatting in these rows is back to it's simplest format.

Thank you in advance!
DCnyc

[JeteMc]

Try replacing the conditional formatting rule for green fill with:

=AND(ISNUMBER(SEARCH("smith, john",$E2)),$F2>=1)

Let us know if you have any questions.