Conditional Formatting degree's w/Tolerance

[AFoxExcel]

Hey all first time posting.

I am working on a document, and need to be able to conditional format numbers, and degree's, along with a +/- tolerance. I was able to get the regular numbers to work, but the fomratting does not seem to work when the degree symbol is added.

Any suggestions on how to get conditional formatting for degree numbers?

The formula I am using current is the first image below:

This is an example of what the sheet looks like:

Any help would be appreciated.

 

[Dossfm0q]

المصنف1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	normal Dimension	÷Tolerance	-Tolerance		Part1	Part2	Part3	Part4	Part5		Part1	Part2	Part3	Part4	Part5
2	90˚	1˚	1˚		91˚	90˚	89˚	91˚	91˚		91˚	92˚	89˚	91˚	91˚
3	1.000	1.010	1.010		1.010	1.020	1.010	1.030	1.010		1.010	1.020	1.010	1.030	1.010
ورقة1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
K2	Expression	=NUMBERVALUE(SUBSTITUTE($K$2,"˚",""))>91	text	NO
E2	Expression	=NUMBERVALUE(SUBSTITUTE($E$2,"˚",""))>91	text	NO
D2	Expression	=NUMBERVALUE(SUBSTITUTE($C$2,"˚",""))>91	text	NO
B2	Expression	=NUMBERVALUE(SUBSTITUTE($A$2,"˚",""))>91	text	NO
C2	Expression	=NUMBERVALUE(SUBSTITUTE($B$2,"˚",""))>91	text	NO
H2	Expression	=NUMBERVALUE(SUBSTITUTE($H$2,"˚",""))>91	text	NO
I2	Expression	=NUMBERVALUE(SUBSTITUTE($I$2,"˚",""))>91	text	NO
J2	Expression	=NUMBERVALUE(SUBSTITUTE($J$2,"˚",""))>91	text	NO
F2	Expression	=NUMBERVALUE(SUBSTITUTE($F$2,"˚",""))>91	text	NO
G2	Expression	=NUMBERVALUE(SUBSTITUTE($G$2,"˚",""))>91	text	NO
N2	Expression	=NUMBERVALUE(SUBSTITUTE($N$2,"˚",""))>91	text	NO
O2	Expression	=NUMBERVALUE(SUBSTITUTE($O$2,"˚",""))>91	text	NO
P2	Expression	=NUMBERVALUE(SUBSTITUTE($P$2,"˚",""))>91	text	NO
L2	Expression	=NUMBERVALUE(SUBSTITUTE($L$2,"˚",""))>91	text	NO
M2	Expression	=NUMBERVALUE(SUBSTITUTE($M$2,"˚",""))>91	text	NO

 

[AFoxExcel]

Thank you for the reply @Dossfm0q, unfortunately this is not giving me exactly what I'm looking for. There will be instances when the "91" will change, so that value needs to be targeting column B (B2 in your example), and then consider the Tolerances which also can vary (Columns C2 & D2). There is also many more rows that will/could be filled in, so writing this formula out for each and every row will not be feasible.

Is there a way to modify my current conditional format to accommodate the degree symbol. I am assuming the symbol is what is throwing the formula off.

 

[Dossfm0q]

hi
1-delete degree symbol
2 apply vba below

Sub NumberFormat()
    Range("A2:O2").NumberFormat = "##" & ChrW(730)
End Sub

المصنف1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	normal Dimension	÷Tolerance	-Tolerance		Part1	Part2	Part3	Part4	Part5		Part1	Part2	Part3	Part4	Part5
2	90˚	1˚	1˚		91˚	90˚	89˚	91˚	91˚		91˚	92˚	89˚	91˚	91˚
3	1	1.01	1.01		1.01	1.02	1.01	1.03	1.01		1.01	1.02	1.01	1.03	1.01
condtio

 

[Fluff]

It can be done, but it depends on what character you are using as the degree sign.
If you point this at a cell with the degree symbol, what does it say

=UNICODE(RIGHT(B2))

 

[AFoxExcel]

It can be done, but it depends on what character you are using as the degree sign.
If you point this at a cell with the degree symbol, what does it say

=UNICODE(RIGHT(B2))

@Fluff, I get a number returned - 176

 

[Fluff]

Ok, you could use

=SUBSTITUTE($B2,CHAR(176),"")+SUBSTITUTE($C2,CHAR(176),"")
=SUBSTITUTE($B2,CHAR(176),"")-ABS(SUBSTITUTE($D2,CHAR(176),""))

 

[AFoxExcel]

Ok, you could use

=SUBSTITUTE($B2,CHAR(176),"")+SUBSTITUTE($C2,CHAR(176),"")
=SUBSTITUTE($B2,CHAR(176),"")-ABS(SUBSTITUTE($D2,CHAR(176),""))

@Fluff , I used your supplied formula's, and converted the numbers to my actual cells. It ended up turning all the numbers red, even the ones that would be within the tolerance limits. See screenshot below.

Thoughts on why it would do that?

If I understand your formula correctly, you are just accommodating for the character.

Here is the conditional format setup I am using and then a confirmation that the formulas are input correctly

1st formula

2nd formula

 

[Fluff]

Ok assuming that G24 is the 1st cell in the applies, Use a formula & use this

=AND(G24<>"",OR(SUBSTITUTE(G24,CHAR(176),"")+0>SUBSTITUTE($B24,CHAR(176),"")+SUBSTITUTE($C24,CHAR(176),""),SUBSTITUTE(G24,CHAR(176),"")+0<SUBSTITUTE($B24,CHAR(176),"")-ABS(SUBSTITUTE($D24,CHAR(176),""))))

+Fluff v2.xlsm
	B	C	D	E	F	G	H	I	J
24	90°	1°	1°			88°	89°	92°	91°
25	1	0.01	0.01			1.02	1	0.99	0.98
Master

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G24:O25	Expression	=AND(G24<>"",OR(SUBSTITUTE(G24,CHAR(176),"")+0>SUBSTITUTE($B24,CHAR(176),"")+SUBSTITUTE($C24,CHAR(176),""),SUBSTITUTE(G24,CHAR(176),"")+0<SUBSTITUTE($B24,CHAR(176),"")-ABS(SUBSTITUTE($D24,CHAR(176),""))))	text	NO

 

[AFoxExcel]

@Fluff, I really really appreciate the time and effort you are putting into this, I wish I understood excel better to work through this myself!

I am still getting some errors in what is being returned, screenshot below is showing a couple test that I tried using your formula. You can see there are some inconsistencies.

Formatting

Formula being used
=AND(G24<>"",OR(SUBSTITUTE(G24,CHAR(176),"")+0>SUBSTITUTE($B24,CHAR(176),"")+SUBSTITUTE($C24,CHAR(176),""),SUBSTITUTE(A24,CHAR(176),"")+0<SUBSTITUTE($B24,CHAR(176),"")-ABS(SUBSTITUTE($D24,CHAR(176),""))))