Conditional Formatting Help Please!

Question

Hi,&nbsp;I am trying to set up conditional formatting for when a number is&nbsp;above or below the mid part of&nbsp;a range.&nbsp;E.g. the lower range is 200 and the upper range is 300, if the number I have is 249 or below I want the cell to highlight green and if it is 250 or above (up to 300) then I want it to highlight yellow.&nbsp;I am sure it is very simple and I am just over complicating it massively! Any help would be greatly appreciated&nbsp;Thanks

rich99 · Answer

Hi David, &nbsp;I used two conditional formats, one for below 250 and one for above, have attached a file rather than trying to explain.&nbsp;&nbsp;

davidsims · Answer

Hi RichThanks for this but maybe I should have been a&nbsp;bit clearer. The upper and lower ranges differ on each row, it might be 200-300 but the next row will be 825-1000, the next row 525-675 etc. I then have a value in another cell and I need to format that cell green if it is closer to the upper range and&nbsp;yellow if it is closer to the lower range&nbsp;Hope that makes sense

saf75 · Answer

the only way i know of as to how to do this would be to use an extra column &amp; add a formula then use that to do your conditional formattingie if column A is you list of numbers have column b as if(a&lt;(half your number),1,0) then have 2 condition formats to work from the 1 or the 0hope this helps :)saf

sergeibaklan · Answer

That could be the rule formula for the green as
=A1&gt;=(MAX($A1:$K1)+MIN($A1:$K1))/2
and similar for another color

&nbsp;

davidsims · Answer

&nbsp;&nbsp;Thanks Sergei but it doesn't seem to be quite what I need (unless I am doing something wrong). If you see the below, column A is what I need formatting - red if it is&nbsp;higher than&nbsp;column D, green if it is equal to or less than column C - this is sorted. What I am struggling with is if&nbsp;column A is closer to&nbsp;column C than to column D, but not exactly between,&nbsp;I want it to colour green but if it is closer to, or exactly between, column D than column C,&nbsp;then&nbsp;I want it to colour yellow. So below A2 should colour green as it is closer to column C and A6 should colour yellow as it is exactly between column C &amp; D. Hopefully&nbsp;some&nbsp;visuals&nbsp;make it easier to understand!&nbsp;&nbsp;

Forum Discussion

Conditional Formatting Help Please!

Resources