Cell colour change relative to value

[GlynB]

HI All. Newbie here and I have, probably what you guys would consider to be simple, a particular problem which I'm struggling to fix.
The first part is simple, even for me, where I would like the value in cell A1 to be subtracted from the value is cell B1 then the resulting value automatically entered into cell C1. Now this, for me, is where it gets complicated. I would then like the value in C1 to be compared to B1 and if the value in C1 differs by more than 5% of B1, cell C1 turns red. Thanks in advance!

 

[etaf]

for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting

Highlight applicable range >>
C1 - Change, reduce or extend the rows to meet your data range of rows

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:
=C1>B1*1.05

Format [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK

This is only if greater -
so if B1 is 100
and C1 is greater than 10 or more it will turn red

if C1 is less than 95 , do you want to colour ???

if so use this formula instead
=OR(C1>B1*1.05,C1<B1*0.95)

Book4
	A	B	C
1		100	92
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C1	Expression	=OR(C1>B1*1.05,C1<B1*0.95)	text	NO

 

[GlynB]

for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting

Highlight applicable range >>
C1 - Change, reduce or extend the rows to meet your data range of rows

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:
=C1>B1*1.05

Format [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK

This is only if greater -
so if B1 is 100
and C1 is greater than 10 or more it will turn red

if C1 is less than 95 , do you want to colour ???

if so use this formula instead
=OR(C1>B1*1.05,C1<B1*0.95)

Book4
	A	B	C
1		100	92
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C1	Expression	=OR(C1>B1*1.05,C1<B1*0.95)	text	NO

Hello Etaf. Many thanks for this. I tried applying the conditional formatting as you described and I chose to use =OR(C1>B1*1.05,C1<B1*0.95) as I wanted to capture the positive and negative difference. Sadly, C1 turns red regardless of value, even if the values in A1 and B1 are identical resulting in. Perhaps I should point out that the value entered into C1 is generated by the formula =B1-A1.

 

[etaf]

ok,
cna you give what eample you are using in A1 and B1

Book4
	A	B	C	D	E
1	92	100	8		TRUE
Sheet1

Cell Formulas
Range	Formula
C1	C1	=B1-A1
E1	E1	=OR(C1>B1*1.05,C1<B1*0.95)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C1	Expression	=OR(C1>B1*1.05,C1<B1*0.95)	text	NO

also shows the formula - just for reference in E1

 

[GlynB]

ok,
cna you give what eample you are using in A1 and B1

Book4
	A	B	C	D	E
1	92	100	8		TRUE
Sheet1

Cell Formulas
Range	Formula
C1	C1	=B1-A1
E1	E1	=OR(C1>B1*1.05,C1<B1*0.95)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C1	Expression	=OR(C1>B1*1.05,C1<B1*0.95)	text	NO

also shows the formula - just for reference in E1

 

[GlynB]

View attachment 87710
View attachment 87711View attachment 87710View attachment 87711

Sorry for the duplicate upload

 

[GlynB]

View attachment 87710
View attachment 87711View attachment 87710View attachment 87711

 

[etaf]

so c1 is 0 which is less than B1

also 10 is less than b1 by more than 5%

maybe i misunderstand

can you just explain the rules with examples

c1 would need to be 95 or 105 to not be coloured , if b1 is 100

10 as a percent of 110 is
10/110
=9%

so well below the 95%

perhaps you need
=ABS(C1/B1)>0.05
to check the % difference is greater than5%

 

[GlynB]

View attachment 87712

so c1 is 0 which is less than B1

also 10 is less than b1 by more than 5%

maybe i misunderstand

can you just explain the rules with examples

c1 would need to be 95 or 105 to not be coloured , if b1 is 100

10 as a percent of 110 is
10/110
=9%

so well below the 95%

Sorry Etaf, I think this is my mistake. If the values in A1 and B1 differ by more than 5% (A1 =100, B1 =95 or A1 =100, B1 =105), I would like C1 to turn red. Huge apologies for the confusion.

 

[etaf]

no problem

so the result in C1 does not matter

compared to which value - B1 or A1 ?

this is the difference compared to B1
=ABS(B1-A1)/B1

assuming A1 = 100 and B1 = 95
=ABS(B1-A1)/A1 = 0.05
BUT

=ABS(B1-A1)/B1 = 0.0526315789473684000000000000000000

as thats 5 but based on b1 which is 95

which may be the same as, just looking at the difference and comparing to B1

=ABS(C1/B1)>0.05

Book4
	A	B	C	D	E	F	G
1	100	105	5.0000		TRUE		FALSE
Sheet1

Cell Formulas
Range	Formula
C1	C1	=B1-A1
E1	E1	=OR(C1>B1*1.05,C1<B1*0.95)
G1	G1	=ABS(C1/B1)>0.05

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C1	Expression	=ABS(C1/B1)>0.05	text	YES