Conditional Format between two values based on another value

**purdue7997** · 04-19-2013, 03:35 AM

This is so easy but I'm clouded right now. I have a value in cell B2 (949). I have two columns of numbers that follow a sequential pattern (numbers get larger, not smaller or mixed). I would like to conditional format the row of numbers in the two columns based on the number in cell B2. So right now the value is 949, I would like row 13 to format because those values are 912 and 1025. My 949 value falls in between them.

I tried using =AND($B$2>=A5,$B$2<=B5) but it's not working properly.

**humdingaling** · 04-19-2013, 03:47 AM

Please Login or Register  to view this content.

works but doesnt work if it equals exactly the number in b2

*edit*
OK does work....numbers are not whole numbers so in essence it is correct

**purdue7997** · 04-19-2013, 03:50 AM

Originally Posted by humdingaling

Please Login or Register  to view this content.

works but doesnt work if it equals exactly the number in b2

Thanks, I had my referencing off. And it is weird it doesn't work if the number is exact. I can work with this for now. Thanks for the help. I'll leave the thread open if anybody else knows the answer.

**Lemice** · 04-19-2013, 03:52 AM

Hello,

Weird, as humdingaling said, it works, and it does for me too
See the sample file attached
TempCF.xlsx

**Fotis1991** · 04-19-2013, 03:53 AM

Perhaps

=AND($B$2>=$A5;$B$2<=$B5)

Edit: Too late reply but just to confirm that this works for me too as Lem sample...

**humdingaling** · 04-19-2013, 03:57 AM

it doesnt work on some of them due to the rounding

342.00

- 113.92
114.92 227.83
228.83 341.75
342.75 455.67
456.67 569.58
570.58 683.50
684.50 797.42
798.42 911.33
912.33 1,025.25
1,026.25 1,139.17
1,140.17 1,253.08
1,254.08 1,367.00

342 doesn't highlight anything because it is greater than 341.75 but lower than 342.75
so it is working as intended