Change background colour of a cell if value is + or - value of another cell

[julhs]

I want to change cell background colour of ANY cell in D5:D15 that is within + or - .05 of B5

B5 = 2.5 (is the reference cell)
D5:D15 = values that change dependent on another cell

In this example,
B5 = 2.5
D11 = 2.52,

So B5+0.05 = 2.55, D11= 2.52 so is below 2.55, so background colour is changed to green
and if the reverse, B5-0.05 = 2.45, D10 = 2.47, so it is above 2.45 then background colour is changed to orange

Col F is some testing of formula as a potential helper column for Condition Formating to use

Book1.xlsm
	A	B	C	D	E	F	G
1
2
3		Quantity		Variable values
4
5		2.5		1.44		below
6				1.62		below
7				1.81		below
8				2.16		below
9				2.34		below
10				2.47		tt
11				2.52		tt
12				2.71		above
13				2.88		above
14				3.06		above
15				3.24		above
16
Sheet1

Cell Formulas
Range	Formula
F5:F15	F5	=IF(D5>($B$5+0.05),"above",IF(D5<($B$5-0.05),"below","tt"))

 

[Joe4]

You can use Conditional Formatting, using two rules:

First rule:
1. Select cells D5:D15
2. Go to Conditional Formatting and choose the formula option (last one)
3. Enter this formula:

AND(($D5-$B$5)>0,($D5-$B$5)<.05)

4. Choose the green formatting color

Second rule:
1. Select cells D5:D15
2. Go to Conditional Formatting and choose the formula option (last one)
3. Enter this formula:

AND(($B$5-$D5)>0,($B$5-$D5)<.05)

4. Choose the orange formatting color

 

[julhs]

That works a treat.!

No way I was going to stumble my way through testing to come up with that as I have done in the past, hence the post!

Couple of somewhat related Condition Formatting questions.
Why when I write a New Condition, done all the necessary stuff, that sometimes the formula appears in the rules with “” around it?

Other is; I’ve noticed that in numerous threads regarding Condition Formatting the “Stop if True” had NOT been ticked. I thought if you didn’t that Condition Formatting was constantly hunting eating up processor resources?

 

[Joe4]

Why when I write a New Condition, done all the necessary stuff, that sometimes the formula appears in the rules with “” around it?

I have not really seen that. I suppose that could happen if you do not start your formula with an equal sign (=).

Other is; I’ve noticed that in numerous threads regarding Condition Formatting the “Stop if True” had NOT been ticked. I thought if you didn’t that Condition Formatting was constantly hunting eating up processor resources?

It certainly would be more efficient if you did check that box. I confess, I seldom do myself, and rarely have performance issues.
I suppose if you had a huge amount of data and/or a lot of rules, it could make a difference.

 

[julhs]

Many thanks Joe4.
Yet again!!; appreciated

 

[Joe4]

You are welcome.
Glad I was able to help!