Conditional Formatting - how to create 3 color scale based on values in each of the columns? Column by column not array

[edss]

I have searched this for quite a bit of time but couldn't find the answer.
I have over hundreds of data, in columns and I want to create 3 color scale based on values in each columns individually (not across all the columns if I just highlight and create 3 color scale in one go).
The only method I knew is to create one column conditional formatting then format painter to each other columns one by one but this looks stupid and very time consuming. Thanks.

This first image is the color scale created across some of the columns together. So some columns will be missing some green colors (for max values)

This second image is what i want, 3 color scale created in each column one by one.

Thanks.

 

[frabulator]

would you be opposed to using VBA?

basically, it is the same thing you described above, but instead of selecting each column yourself, you let excel select the individual columns inside those columns for you.

Sub RunFormattedConditionings()

    Dim Sel As Range
    Set Sel = Selection
    
    Dim Sr As Long
    Dim Er As Long
    
    Dim Sc As Long
    Dim Ec As Long
    
    Sr = Sel.Row
    Er = Sr + Sel.Rows.Count - 1
    
    Sc = Sel.Column
    Ec = Sc + Sel.Columns.Count - 1
    
    For i = Sc To Ec
        Dim rng As Range
        Set rng = Range(Cells(Sr, i), Cells(Er, i))
        rng.FormatConditions.AddColorScale ColorScaleType:=3
        rng.FormatConditions(rng.FormatConditions.Count).SetFirstPriority
        rng.FormatConditions(1).ColorScaleCriteria(3).Type = _
            xlConditionValueHighestValue
        With rng.FormatConditions(1).ColorScaleCriteria(3).FormatColor
            .Color = 8109667
            .TintAndShade = 0
        End With
    Next
    


End Sub

how this code works is by selecting the range of all your data, and running the macro. Excel will loop through each column in the range you selected and change the conditional formatting to only look at one column at a time.

 

[jdellasala]

I am sorry, but using a huge VBA script to solve this problem is just nuts!
To do this quickly and easily, select one entire column (A:A) and apply the conditional formatting. Then double click the Format Painter - this will lock it ON until you hit [Esc]. Now select the rest of the columns one at a time. Hit [Esc] when done. All of the columns will now have the same Conditional Formatting that was applied to column A:A but reference the correct column.

Book1
	A	B	C	D	E	F	G	H	I
1	3%	-7%	1%	3%	5%	9%	-8%	-4%	-9%
2	%	6%	8%	9%	8%	%	6%	-5%	-2%
3	-6%	4%	-6%	4%	-7%	-5%	-3%	-6%	4%
4	-7%	-7%	2%	-2%	9%	8%	-5%	-1%	-2%
5	-3%	4%	5%	6%	%	8%	%	-6%	-5%
6	4%	3%	1%	5%	2%	8%	1%	8%	5%
7	8%	9%	-8%	4%	-1%	1%	-2%	-8%	4%
8	-8%	5%	-4%	-2%	-5%	5%	-2%	8%	1%
9	%	4%	4%	2%	-7%	2%	-6%	-9%	7%
10	-5%	9%	-4%	-8%	-1%	8%	-8%	6%	4%
11	3%	3%	7%	2%	1%	-3%	-6%	-4%	6%
12	-7%	5%	6%	5%	-4%	1%	-4%	-6%	-1%
13	6%	9%	-8%	7%	%	-5%	4%	-3%	-5%
14	2%	-1%	-8%	-8%	6%	-8%	-2%	-8%	-1%
15	9%	-3%	2%	-5%	8%	-1%	9%	9%	9%
16	%	2%	1%	6%	4%	-5%	2%	2%	6%
17	-8%	-7%	-7%	-3%	1%	6%	-7%	-8%	-6%
18	4%	-5%	5%	-6%	-8%	-5%	7%	-1%	-7%
19	-7%	-6%	-8%	3%	-2%	4%	-9%	-2%	-5%
20	3%	8%	2%	3%	8%	6%	-9%	-6%	8%
21
22
23
Sheet1

Cell Formulas
Range	Formula
A1:I20	A1	=RANDARRAY(20,9,-0.09,0.09,0)
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I:I	Other Type	Color scale		NO
H:H	Other Type	Color scale		NO
G:G	Other Type	Color scale		NO
F:F	Other Type	Color scale		NO
E:E	Other Type	Color scale		NO
D:D	Other Type	Color scale		NO
C:C	Other Type	Color scale		NO
B:B	Other Type	Color scale		NO
A:A	Other Type	Color scale		NO

Note that the actual conditional formatting is done for each cell locked - "=$B:$B" not "=B:B" as indicated above, and something I didn't think would work, but the blank cells have no fill. Of course, no promises of what will show up if anything appears below the range in question. However, you can always do a fixed range in the first column like $A1:$A10000 if that becomes a problem, and then instead of selecting the entire column for the Format Painter, just select the range and then click in the first row of the range in each column:

Book1
	K	L	M	N	O	P	Q	R	S
1	-6%	-4%	-5%	7%	8%	-9%	-3%	-7%	-7%
2	6%	-5%	-4%	6%	-1%	%	6%	-3%	3%
3	4%	-9%	-7%	-2%	-7%	2%	-8%	1%	6%
4	-8%	2%	-5%	-3%	1%	8%	-7%	2%	4%
5	7%	-3%	-7%	7%	-1%	-6%	-5%	%	8%
6	-8%	3%	-3%	-4%	1%	-1%	6%	7%	%
7	7%	-1%	3%	-5%	4%	-5%	-4%	-2%	-8%
8	-5%	-3%	8%	8%	-6%	-8%	3%	-4%	6%
9	-8%	7%	-6%	1%	-5%	4%	-3%	-1%	3%
10	6%	-8%	-6%	6%	-4%	3%	-7%	1%	9%
11	1%	2%	-1%	-4%	-1%	-9%	9%	2%	-5%
12	%	-7%	-4%	-8%	-6%	-2%	8%	-1%	1%
13	-4%	-4%	6%	-3%	-2%	-5%	2%	2%	-4%
14	-6%	3%	3%	-6%	-2%	4%	9%	1%	-5%
15	7%	8%	8%	-9%	-5%	-6%	8%	-2%	2%
16	9%	1%	-3%	-1%	1%	1%	7%	9%	-2%
17	-5%	7%	-1%	-1%	-4%	2%	4%	9%	4%
18	9%	-6%	4%	4%	7%	-5%	6%	5%	7%
19	-7%	-3%	9%	-6%	2%	6%	6%	8%	-2%
20	-9%	-4%	-1%	-7%	-4%	-7%	6%	4%	4%
21
22
23
24
25
26
27
28
29
30
Sheet1

Cell Formulas
Range	Formula
K1:S20	K1	=RANDARRAY(20,9,-0.09,0.09,0)
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
S1:S29	Other Type	Color scale		NO
R1:R29	Other Type	Color scale		NO
Q1:Q29	Other Type	Color scale		NO
P1:P29	Other Type	Color scale		NO
O1:O29	Other Type	Color scale		NO
N1:N29	Other Type	Color scale		NO
M1:M29	Other Type	Color scale		NO
L1:L29	Other Type	Color scale		NO
K1:K29	Other Type	Color scale		NO

Again the ranges are locked, and the cell formatting in both samples is custom #%, not blank. Also notice the conditional formatting ends at row 29 in the second sample, so putting a value of 1 in cell K24 which is included in the conditional formatting will totally screw up the colors in K1:K20. K24 will be green, and K1:K20 will all be red to yellow since they're ALL lower than 1!

 

[frabulator]

I am sorry, but using a huge VBA script to solve this problem is just nuts!
To do this quickly and easily, select one entire column (A:A) and apply the conditional formatting. Then double click the Format Painter - this will lock it ON until you hit [Esc]. Now select the rest of the columns one at a time. Hit [Esc] when done. All of the columns will now have the same Conditional Formatting that was applied to column A:A but reference the correct column.

No offense, but how was my code long, and how is your way quicker? They were trying to avoid going through and manually selecting each column individually. Am I misunderstanding?

 

[jdellasala]

No offense, but how was my code long, and how is your way quicker? They were trying to avoid going through and manually selecting each column individually. Am I misunderstanding?

Well for one thing I don't have to go through hoops to save the file as an XLSM file. Also, regardless of how "safe" an Excel file might be, a Macro file is still vulnerable which is why there are so many restrictions on them. Finally, if one wants to do this on another file it would have to start with a copy of the Macro file, or the Macro would need to be copied to the new file.
I notice you don't count on any time you took to develop the script. I didn't know for sure how this would work, but it took more time to get the formatting of the numbers right than figuring out how to get the desired result. And your script requires selecting each range and then executing the script as opposed to double clicking once and then clicking on the range needed until done - a heck of a lot easier and quicker.
No offense, but you really need to ask?

 

[Fluff]

Now select the rest of the columns one at a time.

But that's exactly what the OP wanted to avoid.

And your script requires selecting each range and then executing the script as opposed to double clicking once and then clicking on the range needed until done - a heck of a lot easier and quicker.

I very much doubt that selecting each column manually & using the format painter would be quicker or easier than selecting all the columns & running one simple macro.

 

[frabulator]

Thanks for your insight.

Well for one thing I don't have to go through hoops to save the file as an XLSM file.

You can save it as an XLSM file. But, if this is a one shot thing, just delete the macro when you are done. Then you would not have to worry about it. If you do want to save it though, File> Save As> XLSM is still fewer steps than you suggested.

if one wants to do this on another file it would have to start with a copy of the Macro file, or the Macro would need to be copied to the new file.

You are saying that they would need to follow the same steps? Well... Yes? As they would with yours??

I notice you don't count on any time you took to develop the script. I didn't know for sure how this would work, but it took more time to get the formatting of the numbers right than figuring out how to get the desired result.

Yes, I have tested the script, and it works. I tested it before I posted it, as I do with all my scripts.
And yes, it did take me longer to generate random numbers than it took to format them with conditional formatting. That is precisely why VBA is useful.

Your script requires selecting each range and then executing the script as opposed to double clicking once and then clicking on the range needed until done - a heck of a lot easier and quicker.
No offense, but you really need to ask?

No, using my script you do not need to select each column. You select all the columns and the macro loops them.

It seems to me that you are simply apposed to macros. Which is fine.

It took me less than a second to preform the conditional formatting in the images provided. The clock references how long it took to change tabs to take a picture. (I would upload high quality but the Mr. Excel website would not allow full screen grabs - either case, you get the idea).

So, in short, yes, I really DO need to ask?

 

[jdellasala]

But that's exactly what the OP wanted to avoid.

I very much doubt that selecting each column manually & using the format painter would be quicker or easier than selecting all the columns & running one simple macro.

Errr, that has to be done with the Macro if there's more than a single range as in "Set Sel = Selection".
And you're ready to ignore all of the other issues? Fine. It ends up this question was asked again, but this time for Rows, and the user specified they didn't want to use Format Painter, and I realize that is a PITA. It ends up the solution is even easier than my previous post! Select the first column and apply the conditional formatting as needed. Now select the remaining range and select Paste -> Formatting (R) or use the Paste -> Paste Special... menu item and select Formats from the dialog box.
What's interesting about this technique is that the range the conditional formatting becomes the top left to the bottom right of the range selected, yet the formatting follows the method applied to the original row/column. This table was made using the Format Painter as explained above, manually copying the format to all 49 rows:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	30	8	90	30	12	84	69	76	63	67	13	89	91	94	78	36	20	49	51	56
2	50	29	11	56	43	25	45	39	84	20	31	90	53	75	79	93	13	20	16	11
3	26	43	96	50	50	40	70	85	96	49	28	33	86	11	68	24	33	35	55	51
4	81	19	76	63	40	55	90	55	18	92	93	5	41	85	75	66	52	14	32	66
5	28	33	7	54	26	86	15	9	48	69	52	37	77	50	42	81	56	42	46	13
6	30	68	29	34	31	53	53	8	20	97	68	33	5	69	62	31	42	21	59	9
7	68	56	13	23	96	29	6	23	69	55	15	12	72	8	86	95	57	3	83	38
8	47	68	48	28	15	28	3	27	2	87	28	53	67	31	46	39	16	40	90	66
9	42	63	11	64	61	97	51	76	71	14	56	83	38	77	39	90	43	81	17	100
10	51	98	81	28	69	53	51	37	32	73	42	37	61	72	69	41	87	15	80	96
11	28	67	40	56	2	99	84	9	17	97	47	93	60	80	95	96	92	9	8	53
12	45	90	13	69	4	57	55	33	45	13	96	43	78	84	33	58	44	54	32	21
13	91	49	3	27	33	5	68	79	47	10	29	74	34	63	7	93	4	72	49	97
14	88	73	51	30	24	3	9	45	36	97	33	1	65	100	25	12	18	5	62	31
15	67	74	94	40	19	66	54	51	11	91	39	21	95	59	28	33	53	34	82	22
16	95	68	51	34	14	50	93	32	93	71	25	1	18	62	99	12	21	1	72	70
17	78	83	2	7	30	51	95	95	2	91	94	27	30	75	23	87	15	21	9	50
18	82	20	11	48	23	39	82	77	79	8	33	69	26	77	67	79	53	91	85	51
19	87	19	8	50	36	73	63	81	39	71	37	5	13	24	99	47	76	12	63	8
20	57	5	82	98	91	1	57	62	58	69	85	63	70	36	80	94	11	43	10	52
21	46	82	22	65	41	15	72	85	87	2	1	56	89	69	6	78	5	99	28	78
22	87	33	48	31	87	6	100	20	70	64	25	52	74	43	51	85	4	30	98	77
23	85	48	92	26	10	52	1	95	96	51	98	88	44	75	93	48	83	51	41	97
24	44	32	68	94	72	82	46	59	1	52	90	59	75	22	28	50	80	84	9	82
25	90	64	8	15	76	71	92	30	74	7	20	86	18	37	61	10	49	77	22	54
26	31	38	41	12	52	81	12	29	28	20	95	80	82	16	80	12	97	16	92	77
27	53	86	80	68	55	56	19	6	89	49	58	76	19	72	36	93	89	62	46	36
28	44	47	77	52	37	40	70	13	99	90	37	25	62	39	56	75	35	6	17	50
29	33	29	18	49	18	29	93	83	29	90	2	39	52	18	32	73	7	49	66	69
30	84	26	54	98	88	16	100	56	81	91	22	9	4	20	83	74	93	12	15	86
31	42	88	68	2	25	67	60	91	96	65	50	49	65	40	2	37	1	77	95	96
32	97	32	96	100	77	28	6	92	67	22	56	42	37	64	92	63	79	26	8	56
33	45	37	38	50	88	77	4	6	72	75	8	24	34	28	55	72	79	24	24	57
34	95	53	62	51	64	29	1	56	90	12	81	52	56	95	9	22	75	63	11	98
35	53	53	55	66	51	32	8	44	27	82	33	23	87	60	12	39	50	10	76	54
36	96	27	74	82	60	46	47	14	4	19	58	49	35	11	6	54	75	38	36	29
37	34	65	77	86	77	30	41	42	65	57	26	46	45	26	77	94	47	32	40	45
38	30	44	96	75	37	54	62	80	43	27	60	90	13	67	89	7	87	92	49	9
39	67	50	44	26	44	22	32	60	60	19	69	6	61	8	79	31	47	47	97	93
40	83	7	58	54	76	27	92	19	2	13	4	81	87	52	40	75	8	52	26	59
41	39	63	12	81	36	76	20	77	1	74	90	71	1	23	85	12	5	57	11	10
42	27	100	32	91	92	58	7	10	93	49	9	82	19	41	60	1	53	50	82	16
43	61	52	92	41	88	68	95	27	36	61	97	26	35	21	9	2	73	4	56	91
44	70	36	2	96	11	32	11	78	88	32	36	74	12	87	55	57	55	45	11	74
45	46	30	100	67	49	63	29	49	91	46	39	42	29	88	88	12	44	33	43	24
46	65	74	1	95	3	78	61	29	69	45	85	86	49	55	65	21	63	78	66	74
47	54	78	63	16	45	96	67	47	30	83	18	60	68	71	23	43	85	67	85	73
48	71	92	17	95	74	98	30	28	43	40	56	56	26	31	47	78	54	1	73	5
49	19	15	47	9	53	94	90	28	40	28	46	30	32	84	13	67	69	70	97	32
50	80	23	91	88	84	52	90	32	20	86	9	43	56	18	9	38	80	83	68	27
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A50:T50	Other Type	Color scale		NO
A49:T49	Other Type	Color scale		NO
A48:T48	Other Type	Color scale		NO
A47:T47	Other Type	Color scale		NO
A46:T46	Other Type	Color scale		NO
A45:T45	Other Type	Color scale		NO
A44:T44	Other Type	Color scale		NO
A43:T43	Other Type	Color scale		NO
A42:T42	Other Type	Color scale		NO
A41:T41	Other Type	Color scale		NO
A40:T40	Other Type	Color scale		NO
A39:T39	Other Type	Color scale		NO
A38:T38	Other Type	Color scale		NO
A37:T37	Other Type	Color scale		NO
A36:T36	Other Type	Color scale		NO
A35:T35	Other Type	Color scale		NO
A34:T34	Other Type	Color scale		NO
A33:T33	Other Type	Color scale		NO
A32:T32	Other Type	Color scale		NO
A31:T31	Other Type	Color scale		NO
A30:T30	Other Type	Color scale		NO
A29:T29	Other Type	Color scale		NO
A28:T28	Other Type	Color scale		NO
A27:T27	Other Type	Color scale		NO
A26:T26	Other Type	Color scale		NO
A25:T25	Other Type	Color scale		NO
A24:T24	Other Type	Color scale		NO
A23:T23	Other Type	Color scale		NO
A22:T22	Other Type	Color scale		NO
A21:T21	Other Type	Color scale		NO
A20:T20	Other Type	Color scale		NO
A19:T19	Other Type	Color scale		NO
A18:T18	Other Type	Color scale		NO
A17:T17	Other Type	Color scale		NO
A16:T16	Other Type	Color scale		NO
A15:T15	Other Type	Color scale		NO
A14:T14	Other Type	Color scale		NO
A13:T13	Other Type	Color scale		NO
A12:T12	Other Type	Color scale		NO
A11:T11	Other Type	Color scale		NO
A10:T10	Other Type	Color scale		NO
A9:T9	Other Type	Color scale		NO
A8:T8	Other Type	Color scale		NO
A7:T7	Other Type	Color scale		NO
A6:T6	Other Type	Color scale		NO
A5:T5	Other Type	Color scale		NO
A4:T4	Other Type	Color scale		NO
A3:T3	Other Type	Color scale		NO
A2:T2	Other Type	Color scale		NO
A1:T1	Other Type	Color scale		NO

Notice that the conditional formatting is applied to each row individually.
This table was done copying the first row that had the conditional formatting, and then selecting the other 49 rows of data and using Paste -> Format:

Book1
	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO
1	30	8	90	30	12	84	69	76	63	67	13	89	91	94	78	36	20	49	51	56
2	50	29	11	56	43	25	45	39	84	20	31	90	53	75	79	93	13	20	16	11
3	26	43	96	50	50	40	70	85	96	49	28	33	86	11	68	24	33	35	55	51
4	81	19	76	63	40	55	90	55	18	92	93	5	41	85	75	66	52	14	32	66
5	28	33	7	54	26	86	15	9	48	69	52	37	77	50	42	81	56	42	46	13
6	30	68	29	34	31	53	53	8	20	97	68	33	5	69	62	31	42	21	59	9
7	68	56	13	23	96	29	6	23	69	55	15	12	72	8	86	95	57	3	83	38
8	47	68	48	28	15	28	3	27	2	87	28	53	67	31	46	39	16	40	90	66
9	42	63	11	64	61	97	51	76	71	14	56	83	38	77	39	90	43	81	17	100
10	51	98	81	28	69	53	51	37	32	73	42	37	61	72	69	41	87	15	80	96
11	28	67	40	56	2	99	84	9	17	97	47	93	60	80	95	96	92	9	8	53
12	45	90	13	69	4	57	55	33	45	13	96	43	78	84	33	58	44	54	32	21
13	91	49	3	27	33	5	68	79	47	10	29	74	34	63	7	93	4	72	49	97
14	88	73	51	30	24	3	9	45	36	97	33	1	65	100	25	12	18	5	62	31
15	67	74	94	40	19	66	54	51	11	91	39	21	95	59	28	33	53	34	82	22
16	95	68	51	34	14	50	93	32	93	71	25	1	18	62	99	12	21	1	72	70
17	78	83	2	7	30	51	95	95	2	91	94	27	30	75	23	87	15	21	9	50
18	82	20	11	48	23	39	82	77	79	8	33	69	26	77	67	79	53	91	85	51
19	87	19	8	50	36	73	63	81	39	71	37	5	13	24	99	47	76	12	63	8
20	57	5	82	98	91	1	57	62	58	69	85	63	70	36	80	94	11	43	10	52
21	46	82	22	65	41	15	72	85	87	2	1	56	89	69	6	78	5	99	28	78
22	87	33	48	31	87	6	100	20	70	64	25	52	74	43	51	85	4	30	98	77
23	85	48	92	26	10	52	1	95	96	51	98	88	44	75	93	48	83	51	41	97
24	44	32	68	94	72	82	46	59	1	52	90	59	75	22	28	50	80	84	9	82
25	90	64	8	15	76	71	92	30	74	7	20	86	18	37	61	10	49	77	22	54
26	31	38	41	12	52	81	12	29	28	20	95	80	82	16	80	12	97	16	92	77
27	53	86	80	68	55	56	19	6	89	49	58	76	19	72	36	93	89	62	46	36
28	44	47	77	52	37	40	70	13	99	90	37	25	62	39	56	75	35	6	17	50
29	33	29	18	49	18	29	93	83	29	90	2	39	52	18	32	73	7	49	66	69
30	84	26	54	98	88	16	100	56	81	91	22	9	4	20	83	74	93	12	15	86
31	42	88	68	2	25	67	60	91	96	65	50	49	65	40	2	37	1	77	95	96
32	97	32	96	100	77	28	6	92	67	22	56	42	37	64	92	63	79	26	8	56
33	45	37	38	50	88	77	4	6	72	75	8	24	34	28	55	72	79	24	24	57
34	95	53	62	51	64	29	1	56	90	12	81	52	56	95	9	22	75	63	11	98
35	53	53	55	66	51	32	8	44	27	82	33	23	87	60	12	39	50	10	76	54
36	96	27	74	82	60	46	47	14	4	19	58	49	35	11	6	54	75	38	36	29
37	34	65	77	86	77	30	41	42	65	57	26	46	45	26	77	94	47	32	40	45
38	30	44	96	75	37	54	62	80	43	27	60	90	13	67	89	7	87	92	49	9
39	67	50	44	26	44	22	32	60	60	19	69	6	61	8	79	31	47	47	97	93
40	83	7	58	54	76	27	92	19	2	13	4	81	87	52	40	75	8	52	26	59
41	39	63	12	81	36	76	20	77	1	74	90	71	1	23	85	12	5	57	11	10
42	27	100	32	91	92	58	7	10	93	49	9	82	19	41	60	1	53	50	82	16
43	61	52	92	41	88	68	95	27	36	61	97	26	35	21	9	2	73	4	56	91
44	70	36	2	96	11	32	11	78	88	32	36	74	12	87	55	57	55	45	11	74
45	46	30	100	67	49	63	29	49	91	46	39	42	29	88	88	12	44	33	43	24
46	65	74	1	95	3	78	61	29	69	45	85	86	49	55	65	21	63	78	66	74
47	54	78	63	16	45	96	67	47	30	83	18	60	68	71	23	43	85	67	85	73
48	71	92	17	95	74	98	30	28	43	40	56	56	26	31	47	78	54	1	73	5
49	19	15	47	9	53	94	90	28	40	28	46	30	32	84	13	67	69	70	97	32
50	80	23	91	88	84	52	90	32	20	86	9	43	56	18	9	38	80	83	68	27
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
V2:AO50	Other Type	Color scale		NO
V1:AO1	Other Type	Color scale		NO

AND, as it ends up, this was all in vain. Selecting the entire range at once produces the identical results as the other two methods:

Book1
	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ
1	30	8	90	30	12	84	69	76	63	67	13	89	91	94	78	36	20	49	51	56
2	50	29	11	56	43	25	45	39	84	20	31	90	53	75	79	93	13	20	16	11
3	26	43	96	50	50	40	70	85	96	49	28	33	86	11	68	24	33	35	55	51
4	81	19	76	63	40	55	90	55	18	92	93	5	41	85	75	66	52	14	32	66
5	28	33	7	54	26	86	15	9	48	69	52	37	77	50	42	81	56	42	46	13
6	30	68	29	34	31	53	53	8	20	97	68	33	5	69	62	31	42	21	59	9
7	68	56	13	23	96	29	6	23	69	55	15	12	72	8	86	95	57	3	83	38
8	47	68	48	28	15	28	3	27	2	87	28	53	67	31	46	39	16	40	90	66
9	42	63	11	64	61	97	51	76	71	14	56	83	38	77	39	90	43	81	17	100
10	51	98	81	28	69	53	51	37	32	73	42	37	61	72	69	41	87	15	80	96
11	28	67	40	56	2	99	84	9	17	97	47	93	60	80	95	96	92	9	8	53
12	45	90	13	69	4	57	55	33	45	13	96	43	78	84	33	58	44	54	32	21
13	91	49	3	27	33	5	68	79	47	10	29	74	34	63	7	93	4	72	49	97
14	88	73	51	30	24	3	9	45	36	97	33	1	65	100	25	12	18	5	62	31
15	67	74	94	40	19	66	54	51	11	91	39	21	95	59	28	33	53	34	82	22
16	95	68	51	34	14	50	93	32	93	71	25	1	18	62	99	12	21	1	72	70
17	78	83	2	7	30	51	95	95	2	91	94	27	30	75	23	87	15	21	9	50
18	82	20	11	48	23	39	82	77	79	8	33	69	26	77	67	79	53	91	85	51
19	87	19	8	50	36	73	63	81	39	71	37	5	13	24	99	47	76	12	63	8
20	57	5	82	98	91	1	57	62	58	69	85	63	70	36	80	94	11	43	10	52
21	46	82	22	65	41	15	72	85	87	2	1	56	89	69	6	78	5	99	28	78
22	87	33	48	31	87	6	100	20	70	64	25	52	74	43	51	85	4	30	98	77
23	85	48	92	26	10	52	1	95	96	51	98	88	44	75	93	48	83	51	41	97
24	44	32	68	94	72	82	46	59	1	52	90	59	75	22	28	50	80	84	9	82
25	90	64	8	15	76	71	92	30	74	7	20	86	18	37	61	10	49	77	22	54
26	31	38	41	12	52	81	12	29	28	20	95	80	82	16	80	12	97	16	92	77
27	53	86	80	68	55	56	19	6	89	49	58	76	19	72	36	93	89	62	46	36
28	44	47	77	52	37	40	70	13	99	90	37	25	62	39	56	75	35	6	17	50
29	33	29	18	49	18	29	93	83	29	90	2	39	52	18	32	73	7	49	66	69
30	84	26	54	98	88	16	100	56	81	91	22	9	4	20	83	74	93	12	15	86
31	42	88	68	2	25	67	60	91	96	65	50	49	65	40	2	37	1	77	95	96
32	97	32	96	100	77	28	6	92	67	22	56	42	37	64	92	63	79	26	8	56
33	45	37	38	50	88	77	4	6	72	75	8	24	34	28	55	72	79	24	24	57
34	95	53	62	51	64	29	1	56	90	12	81	52	56	95	9	22	75	63	11	98
35	53	53	55	66	51	32	8	44	27	82	33	23	87	60	12	39	50	10	76	54
36	96	27	74	82	60	46	47	14	4	19	58	49	35	11	6	54	75	38	36	29
37	34	65	77	86	77	30	41	42	65	57	26	46	45	26	77	94	47	32	40	45
38	30	44	96	75	37	54	62	80	43	27	60	90	13	67	89	7	87	92	49	9
39	67	50	44	26	44	22	32	60	60	19	69	6	61	8	79	31	47	47	97	93
40	83	7	58	54	76	27	92	19	2	13	4	81	87	52	40	75	8	52	26	59
41	39	63	12	81	36	76	20	77	1	74	90	71	1	23	85	12	5	57	11	10
42	27	100	32	91	92	58	7	10	93	49	9	82	19	41	60	1	53	50	82	16
43	61	52	92	41	88	68	95	27	36	61	97	26	35	21	9	2	73	4	56	91
44	70	36	2	96	11	32	11	78	88	32	36	74	12	87	55	57	55	45	11	74
45	46	30	100	67	49	63	29	49	91	46	39	42	29	88	88	12	44	33	43	24
46	65	74	1	95	3	78	61	29	69	45	85	86	49	55	65	21	63	78	66	74
47	54	78	63	16	45	96	67	47	30	83	18	60	68	71	23	43	85	67	85	73
48	71	92	17	95	74	98	30	28	43	40	56	56	26	31	47	78	54	1	73	5
49	19	15	47	9	53	94	90	28	40	28	46	30	32	84	13	67	69	70	97	32
50	80	23	91	88	84	52	90	32	20	86	9	43	56	18	9	38	80	83	68	27
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AQ1:BJ50	Other Type	Color scale		NO

I'm not sure I understand why all three conditional format ranges behave the same, but they do appear to! I scanned most of the rows of all three tables, and as far as I can tell they're all the same even though the ranges that the conditional formatting is being applied to are different. I think (know?) the reason why it's working is because the color scale is based on Percentile and not the numbers themselves although percent worked as well.

 

[frabulator]

If you want to do this with rows you can change my Macro around to loop through them as well. Just replace:

    For i = Sc To Ec
        Dim rng As Range
        Set rng = Range(Cells(Sr, i), Cells(Er, i))

with

    For i = Sr To Er
        Dim rng As Range
        Set rng = Range(Cells(i, Sc), Cells(i, Ec))

 

[edss]

I am sorry, but using a huge VBA script to solve this problem is just nuts!
To do this quickly and easily, select one entire column (A:A) and apply the conditional formatting. Then double click the Format Painter - this will lock it ON until you hit [Esc]. Now select the rest of the columns one at a time. Hit [Esc] when done. All of the columns will now have the same Conditional Formatting that was applied to column A:A but reference the correct column.

Book1
	A	B	C	D	E	F	G	H	I
1	3%	-7%	1%	3%	5%	9%	-8%	-4%	-9%
2	%	6%	8%	9%	8%	%	6%	-5%	-2%
3	-6%	4%	-6%	4%	-7%	-5%	-3%	-6%	4%
4	-7%	-7%	2%	-2%	9%	8%	-5%	-1%	-2%
5	-3%	4%	5%	6%	%	8%	%	-6%	-5%
6	4%	3%	1%	5%	2%	8%	1%	8%	5%
7	8%	9%	-8%	4%	-1%	1%	-2%	-8%	4%
8	-8%	5%	-4%	-2%	-5%	5%	-2%	8%	1%
9	%	4%	4%	2%	-7%	2%	-6%	-9%	7%
10	-5%	9%	-4%	-8%	-1%	8%	-8%	6%	4%
11	3%	3%	7%	2%	1%	-3%	-6%	-4%	6%
12	-7%	5%	6%	5%	-4%	1%	-4%	-6%	-1%
13	6%	9%	-8%	7%	%	-5%	4%	-3%	-5%
14	2%	-1%	-8%	-8%	6%	-8%	-2%	-8%	-1%
15	9%	-3%	2%	-5%	8%	-1%	9%	9%	9%
16	%	2%	1%	6%	4%	-5%	2%	2%	6%
17	-8%	-7%	-7%	-3%	1%	6%	-7%	-8%	-6%
18	4%	-5%	5%	-6%	-8%	-5%	7%	-1%	-7%
19	-7%	-6%	-8%	3%	-2%	4%	-9%	-2%	-5%
20	3%	8%	2%	3%	8%	6%	-9%	-6%	8%
21
22
23
Sheet1

Cell Formulas
Range	Formula
A1:I20	A1	=RANDARRAY(20,9,-0.09,0.09,0)
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I:I	Other Type	Color scale		NO
H:H	Other Type	Color scale		NO
G:G	Other Type	Color scale		NO
F:F	Other Type	Color scale		NO
E:E	Other Type	Color scale		NO
D:D	Other Type	Color scale		NO
C:C	Other Type	Color scale		NO
B:B	Other Type	Color scale		NO
A:A	Other Type	Color scale		NO

Note that the actual conditional formatting is done for each cell locked - "=$B:$B" not "=B:B" as indicated above, and something I didn't think would work, but the blank cells have no fill. Of course, no promises of what will show up if anything appears below the range in question. However, you can always do a fixed range in the first column like $A1:$A10000 if that becomes a problem, and then instead of selecting the entire column for the Format Painter, just select the range and then click in the first row of the range in each column:

Book1
	K	L	M	N	O	P	Q	R	S
1	-6%	-4%	-5%	7%	8%	-9%	-3%	-7%	-7%
2	6%	-5%	-4%	6%	-1%	%	6%	-3%	3%
3	4%	-9%	-7%	-2%	-7%	2%	-8%	1%	6%
4	-8%	2%	-5%	-3%	1%	8%	-7%	2%	4%
5	7%	-3%	-7%	7%	-1%	-6%	-5%	%	8%
6	-8%	3%	-3%	-4%	1%	-1%	6%	7%	%
7	7%	-1%	3%	-5%	4%	-5%	-4%	-2%	-8%
8	-5%	-3%	8%	8%	-6%	-8%	3%	-4%	6%
9	-8%	7%	-6%	1%	-5%	4%	-3%	-1%	3%
10	6%	-8%	-6%	6%	-4%	3%	-7%	1%	9%
11	1%	2%	-1%	-4%	-1%	-9%	9%	2%	-5%
12	%	-7%	-4%	-8%	-6%	-2%	8%	-1%	1%
13	-4%	-4%	6%	-3%	-2%	-5%	2%	2%	-4%
14	-6%	3%	3%	-6%	-2%	4%	9%	1%	-5%
15	7%	8%	8%	-9%	-5%	-6%	8%	-2%	2%
16	9%	1%	-3%	-1%	1%	1%	7%	9%	-2%
17	-5%	7%	-1%	-1%	-4%	2%	4%	9%	4%
18	9%	-6%	4%	4%	7%	-5%	6%	5%	7%
19	-7%	-3%	9%	-6%	2%	6%	6%	8%	-2%
20	-9%	-4%	-1%	-7%	-4%	-7%	6%	4%	4%
21
22
23
24
25
26
27
28
29
30
Sheet1

Cell Formulas
Range	Formula
K1:S20	K1	=RANDARRAY(20,9,-0.09,0.09,0)
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
S1:S29	Other Type	Color scale		NO
R1:R29	Other Type	Color scale		NO
Q1:Q29	Other Type	Color scale		NO
P1:P29	Other Type	Color scale		NO
O1:O29	Other Type	Color scale		NO
N1:N29	Other Type	Color scale		NO
M1:M29	Other Type	Color scale		NO
L1:L29	Other Type	Color scale		NO
K1:K29	Other Type	Color scale		NO

Again the ranges are locked, and the cell formatting in both samples is custom #%, not blank. Also notice the conditional formatting ends at row 29 in the second sample, so putting a value of 1 in cell K24 which is included in the conditional formatting will totally screw up the colors in K1:K20. K24 will be green, and K1:K20 will all be red to yellow since they're ALL lower than 1!

Thanks. I don't know if i understand completely. I tried your method and seems it doesn't work.
What I did is simply create a 3 color scale for column A, and double clicked "Format Painter", and paste across the rest of the columns in one go. The 3 color scale in the rest of the columns are still treated as an array rather than individual columns.
I am not sure if I missed some steps from text, but I don't quite understand the paragraph "Note that the actual conditional formatting is done for each cell locked - "=$B:$B" not "=B:B" as indicated above... "

For example, I have created the 3 color scale in the column A below and do format painter to column B to G in one go. As you can see, column B to G conditional formatting is still reading the data together, especially in the case if the max value in one column stand out particularly.