Forum Discussion
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.
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.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)
