# cell between two number values results in color change of a neighboring cell with certain value.

Copper Contributor

# cell between two number values results in color change of a neighboring cell with certain value.

I need help, I am a beginner to formatting in excel and we want to change the color of cell I2 if the value of cell H2 is between two numbers. We have a spreadsheet at work where we enter lab results, and there are three ranges that need to be identified and occasionally o

so

if H2 has a range of 10.5 to11.0 then I2 must have a value of 9.0 or greater to pass and

if H2 has a range of 11.1 to 11.5 then I2 must have a value of 8.5 or greater to pass and

if H2 has greater or equal to 11.5 then I2 must have a value of 8.0 or greater to pass.

2 Replies

# Re: cell between two number values results in color change of a neighboring cell with certain value.

You didn't specify what and how to color. As variant

with rule formula

``=(\$H2>=10.5)*(\$H2<11.1)*(\$I2>=9) +(\$H2>=11.1)*(\$H2<11.5)*(\$I2>=8.5) +(\$H2>=11.5)*(\$I2>=8)``

# Re: cell between two number values results in color change of a neighboring cell with certain value.

Select I2.

Set the fill color to red. This will be the default.

On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula

=I2>=IF(H2>=11.5, 8, IF(H2>11, 8.5, IF(H2>10.5, 9, 10000000)))

(Perhaps H2>10.5 should be H2>=10.5, that's not entirely clear to me)

Click Format...
Activate the Fill tab.
Select green as color.
Click OK, then click OK again.