Conditional Formatting Rows with Max and Min Specs

[jakeneedshelp]

Hey all,
Let me start by pre-apologizing if this topic has been hammered to death. I tried a quick search but didnt see what i was looking for.

Basically I'm trying to apply conditional formatting on a row of measured values to see which fall under a preset MAX and MIN specification. I would like for the values below the MIN to have red fill that gets darker the lower below the MIN spec they go. Ditto for the MAX except I would like them to become yellow if > the MAX spec and get darker the higher they go above the MAX.


Currently I'm using two rules of conditional formatting on the rows using the basic Greater than and Less than rules but these only color cells red or yellow depending on if they fall below/over the spec(i e they dont go darker the farther away from the spec). Heres an example of my data.

Also I'm wondering if theres a simple way to apply this formatting to the whole block of data. Currently I've conditionally formatted a row and marked some cells to remain constant, then copied and done a Paste Special>formatting on each row in the block(yes it gets tedious after a while....)

\1

Thanks for reading and I look forward to any recommendations!

[dilipandey]

Hi jakeneedshelp

Welcome to the forum.
This seems to be achievable.. please upload a sample workbook with dummy data. Thanks.

Regards,
DILIPandey


<click on below 'star' if this helps>

[Ace_XL]

Use two conditional formatting rules...in the following order

1. =AND(A2>10,A2<25)--> No format; where 10 is your minimum value and 25 is your maximum value. This will ensure cells between the MIN & MAX function are not formatted at all
2. 2-color scale--> Minimum value --red; Maximum value -- yellow

Does this work for you?

[jakeneedshelp]

Thank you both very much for the replies. Ace, I wasn't able to adapt your post properly. Could you make the formula more specific to the dummy data below?

I've attached a set of dummy data as suggested, hope it helps.
example data.xlsx

Thanks a million!

[Ace_XL]

Presumably, the mIN and Max values are for each row. Please see attached. Does this work for you?

[jakeneedshelp]

Presumably, the mIN and Max values are for each row. Please see attached. Does this work for you?

Thanks you for the reply Ace. You are correct, the min and max in the three far right columns are the min and max specific to that row.

This is VERY close to what I was looking for. Just a couple things: For example, the difference between cell R3 and its respective minimum(S3) is .0008. This difference is the same as the difference(.0008) between cell R9 and its respective minimum (S9) yet cell R9 is not as dark red as cell R3. By comparison cell R5 is only .0001 under its minimum(cell S5) yet as dark and red as cell R3. And vice versa for the MAX values(ex J4-U4 has a larger difference than J8-U8, therefore J4 should be a much darker yellow(rather than orange) and J8 should be a very light yellow(trending towards white). I was hoping to have it where each row understands the magnitude it is away from its respective min(or max) but that difference is graded as a whole relative to the other rows. I hope that makes sense and I'm not aimlessly rambling!

Thanks you so much for your help thus far. I'm not very familiar with the AND function and would not have even been able to get that!

[Ace_XL]

The graded conditional formatting is a 2-sacle defined to work between minimum and maximum values of the whole range (whole row in your case). The way it is set up is that it does not even consider the minimum and maximum values that you have to the righmost.

The first condition excludes these values (between your defined MAX & MIN values) from formatting. Hence, in certain cases, the deviation of a value from the range minimum and your defined minumum would not be consistent and hence the anomaly. I am not sure the graded scale can be applied in another way!

Hope this helps!