Macro: Conditional Formatting on Multiple Columns

[excelnovice123]

Hi guys,

I am trying to create a macro that will apply a "Bottom 10%" Conditional Formatting rule to dozens of columns in a spreadsheet. I would like all columns to use the bottom 10% rule, but bottom 10% values should be relative only to the other cells in that column--not the other cells in the array.

Ideally, the macro would be able to automatically detect all of the populated columns in the spreadsheet and apply the Bottom 10% rule to each of those columns separately from one another, on a column by column basis.

I hope this makes sense, and thank you in advance!!

-Excel Novice

 

[Sal Paradise]

Hi guys,

I am trying to create a macro that will apply a "Bottom 10%" Conditional Formatting rule to dozens of columns in a spreadsheet. I would like all columns to use the bottom 10% rule, but bottom 10% values should be relative only to the other cells in that column--not the other cells in the array.

Ideally, the macro would be able to automatically detect all of the populated columns in the spreadsheet and apply the Bottom 10% rule to each of those columns separately from one another, on a column by column basis.

I hope this makes sense, and thank you in advance!!

-Excel Novice

Excel 2003
	A	B	C	D
1	Column 1	Column 2	Column 3	Column 4
2	868	507	637	690
3	2	572	505	812
4	373	133	483	110
5	646	160	760	147
6	861	918	361	655
7	629	708	839	203
8	988	310	681	926
9	305	286	285	822
10	604	110	82	697
11	177	195	87	100
12	711	207	562	510
13	845	713	251	462
14	673	981	628	849
15	539	823	444	519
16	640	761	21	207
17	95		106	92
18	387		600	667
19	606		377	107
20	722		870
21	846		209
22	136		249
23	689		228
24	768		267
25	222		691
26	844		55
27	272		436
28	93
29	317
30	840
31	623
32	975
33	915
34	29
35	501
Sheet1

In B2 (and will automatically work for any column):
=AND(A2<>"",A2<=SMALL(OFFSET(A$1,1,0,COUNTA(A:A)-1,1),ROUND(COUNTA(OFFSET(A$1,1,0,COUNTA(A:A)-1,1))/10,0)))