Conditional formatting abs Val > 1 <2 with color scale

[brenmacd]

Hello

I am trying to color my cell based on the absolute value of the contents of that cell such that: the cell is green if less than one, gradually turns from green to red between 1 and 2, and red if greater than 2. Thanks!

 

[Asbestos_Jen]

Is this the sort of thing you're after?

 

[brenmacd]

Yes but I have to deal with negative numbers so need to consider absolute values. Thank you for your reply!

 

[Eric W]

Attempt #1:

Book2 (version 1).xlsb
	A	B
1
2
3
4		-3
5		-2.7
6		-2.4
7		-2.1
8		-1.8
9		-1.5
10		-1.2
11		-0.9
12		-0.6
13		-0.3
14		0
15		0.3
16		0.6
17		0.9
18		1.2
19		1.5
20		1.8
21		2.1
22
Sheet3

Cell Formulas
Range	Formula
B4:B21	B4	=SEQUENCE(18,,-3,0.3)
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B4:B21	Expression	=ABS(B4)>2	text	YES
B4:B21	Expression	=ABS(B4)<1	text	YES
B4:B21	Other Type	Color scale		NO

I used 3 rules to accomplish this, and yes, the range -2 to -1 is not correct. I have 2 rules for ABS()<1 and ABS()>2, and one rule for a color scale between 1 and 2. I thought I could add another color scale rule between -2 and -1, but no such luck. The color scale rule prevents any other rules below it from working. So I added a few more rules, and while not ideal, kinda works:

Attempt #2:

Book2 (version 1).xlsb
	A	B
1
2
3
4		-3
5		-2.7
6		-2.4
7		-2.1
8		-1.8
9		-1.5
10		-1.2
11		-0.9
12		-0.6
13		-0.3
14		0
15		0.3
16		0.6
17		0.9
18		1.2
19		1.5
20		1.8
21		2.1
22
Sheet5

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B4:B21	Expression	=ABS(B4)>2	text	YES
B4:B21	Expression	=ABS(B4)<1	text	YES
B4:B21	Expression	=B4<-1.7	text	YES
B4:B21	Expression	=B4<-1.3	text	YES
B4:B21	Expression	=B4<-1	text	YES
B4:B21	Other Type	Color scale		NO

The color transition is not as smooth. You could probably get something a bit better with VBA.

 

[brenmacd]

Thanks