Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

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

Copper Contributor

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

@Antol78 

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

image.png

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)

@Antol78 

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.