Multiple Conditional Formatting Options ...

[Russ At Index]

Hello Excel World ,

Once again the management have tasked something & i really don't know where to begin .. so here goes.

A table detailing number of sales from each location . So each cell has a number within it (or it is blank if the
formula result is 0). These cells have been conditionally formatted into four "static" conditions:
Cell value is blank = Blue
<50 = Red
>50,<100 = Amber
>100= Green
So four conditions , and it works perfectly .
Powers that be would like to be able to re model the formatting , for example change the less than , greater than
quantities but keep the same colours.
My question is , is there any way to either dynamically change the rule , or is there an option to have several rules
and you could select that rule ?.
Any ideas , as always gratefully received..
Many thanks ,
Russ.

 

[Fluff]

You could use a couple of helper cells where you input the lower & upper values

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
7	55	88	43	51	116	2	98	38	130	36	Lower	50
8	100	104	125	64	149	111	63	101	131	85	Upper	100
9	16	17	7	78	31	37	100	5	53	106
10	44	13	60	110	115	18	120	18	117	25
11	96	75	47	26	144	139	140	56	34	25
12	136	86	109	121	145	113	67	47	94	143
13	68	70	75	105	11	5	103	40	124	143
14	30	50	132	112	124	25	37	137	98	82
15	124	18	20	148	62	111	121	91	38	5
16	148	108	89	138	91	148	48	8	92	132
Main

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A7:J16	Expression	=A7<=$L$7	text	NO
A7:J16	Expression	=A7>$L$8	text	NO
A7:J16	Expression	=A7<=$L$8	text	NO

 

[etaf]

you could refer to a different sheet for a range of values to use , so that things can be changed

otherwise, can you give more examples of how to dynamically change the range

so you could have 2 cells
one with 50 in and 1 with 100

then say thats in sheet2 cell A1 and A2

then
<sheet2!$A$1 = Red
>sheet2!$A$1,<sheet2!$A$2 = Amber
>sheet2!$A$2 = Green

But you may need to change the conditional formatting to use formulas

 

[Russ At Index]

Hi Fluff ,
Many thanks for the prompt response , i will give your helper cell solution a whirl and let you know how
i get on .
Have a good & safe weekend,
Russ.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Russ At Index]

Hi Fluff ,
Sorry for the delay in testing Mate ,
Both green & red formatting are working just fine , but alas the amber function is not ....
Any ideas
Thanks
Russ.

 

[Fluff]

Can you post some sample data showing the problem.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Russ At Index]

Hi Fluff ,
Mini sheet attached

BHX-HeatMap_V1.4_Nov_Dec_Jan-CF Test.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Min	50			Aisle	41		84	83	82
2	Max	99					Nov	55	88	43
3							Dec	100	104	125
4							Jan	16	17	7
HeatMapOPA-PIC01-QPA

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H2:J4	Expression	=H2<=$B$2	text	NO
H2:J4	Expression	=H2>$B$2	text	NO
H2:J4	Expression	=H2<=$B$1	text	NO

 

[Fluff]

Thanks for that, you need to move the rule for the red cells, above the Amber rule.

 

[Russ At Index]

Thanks for that, you need to move the rule for the red cells, above the Amber rule.

Excellent , saved a considerable amount of grief , thank you very much indeed .

Stay safe , and stay sane ,
Russ.