How to apply one conditional formatting rule to several ranges?

Sam Hamels

New Member
Joined
Mar 20, 2018
Messages
47
Let's say I have three columns:

A1:A10
B1:B10
C1:C10

Each column has its own data (for example, some numbers between 1 and 100)

I want to use conditional formatting with a two-color scale to visualize the lowest-to-highest values in each column
This can easily be achieved by setting up the same conditional formatting (with the same 'rule' and two-color scale) for each column separately

However, when you have a large amount of columns, this becomes cumbersome.
Especially if, for example, you want to change the colors used in the two-color scales.
I would prefer to have one rule that applies to every column separately, so I can change the applied rule or the colors once for all the separate columns.

I dont' want to simply select all columns at once (A1:C10) and then set up the conditional formatting, because the two-color scale would then be applied by looking at the values of that entire range, instead of considering the data in each column separately.


Cheers,
Sam
 
Last edited:

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,343
Office Version
365
Platform
Windows
This method works for me

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

I created 2 rules - alternating colours by column
You may prefer to use more colours, but the same basic logic applies - simply use more named ranges and a CF rule for each range

Suggest you test this method in a NEW workbook, with a few columns of data and amend to suit your own needs afterwards

With 2 rules


1. Create 2 named ranges (Colour1 and Colour2) - one for each colour-scale
Hold down the {CTRL} key while selecting Columns B, D, F, H, J, L, N ... before typing Colour1 in the Name box and {ENTER}
Hold down the {CTRL} key while selectingt Columns C, E, G, I, K, M, O ... before typing Colour2 in the Name box and {ENTER}

2. Now you can select ALL the cells in each range by simply typing the name in the Name box and {ENTER} and they can be conditionally formatted together

3. Create 2 new CF rules (each with its own 2 Colour-Scale) formatting, using contrasting colour-combos for the 2 ranges


NOTE - the Name Box is the input box immediately above cell A1
 

Sam Hamels

New Member
Joined
Mar 20, 2018
Messages
47
Hi Yongle,


Thank for the reply!

As far as I can see, using named ranges in this way does not solve my problem.
The multiple columns that are included within each named range are still evaluated as a single range when determining the conditional formatting.

See for example this example:



Range_A = A1:A15 ; C1:C15
Range_B = B1:B15 ; D1:D15

Columns A and B are values between 1 and 10
Columns C and D are values between 1 and 1000

The problem remains: the values of column A and B are comparatively very small within their own named ranges (Range_A and Range_B), due to the much higher values in columns C and D.
What I need is a color scheme that is set up independently for every column. In other words, the result in the example above should be that the data in columns A and B is also colored based on their own values (i.e. values close to 10 will get a dark red/green color).

I know this can be achieved by setting up an individual custom formatting rule & range for every column, but this is very cumbersome when you have a lot of columns.
Moreover, it is then also very cumbersome to make changes to the color scheme (since you will also have to do it separately over and over again for every CF range/rule).
 
Last edited:

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,343
Office Version
365
Platform
Windows
The multiple columns that are included within each named range are still evaluated as a single range when determining the conditional formatting
oh - I did not even consider that issue !

what happens if ...
... create 2 CF RULES one for columnB and one for columnC
... and then copy columnB and paste special format to columnD
... same for C to D

( Away from PC so cannot test )
 

Sam Hamels

New Member
Joined
Mar 20, 2018
Messages
47
Doing that creates new (additional) conditional formatting ranges/rules for the columns where you pasted the formatting.
It's an easier way to set up the conditional formatting for additional columns, but it doesn't solve the problem unfortunately.
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,343
Office Version
365
Platform
Windows
But I think you must have a separate rule for each range in this instance - otherwise your other problem returns
I will have a try at something to develop it further when I am back at my PC

which specific issues does that give you other than a maintenance issue when changing the colour scheme ?
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,343
Office Version
365
Platform
Windows
I think you require VBA to resolve your issue

Here is some sample code that you can adapt to suit your own circumstances
- create 2 X one cell named ranes per rule (one for high colour and one for low colour)
- colour the cells as you please
- the values in the array are the FIRST cell in each column to apply the rule to
- cel.Resize(100) makes the rule apply to 100 rows ( A1 resized become A1:A100)

NOTES
I used 2 rules and created 4 named ranges. Each range contains one cell
Low_1 and High_1
Low_2 and High_2


Code:
Sub LoopRangesToFormat()
    Dim ref As Variant
'first range of columns
    For Each ref In Array("A1", "C1", "E1", "G1", "I1", "K1")
       Call AmendConditions(Range(ref), [Low_1], [High_1])
    Next ref
'second range of columns
    For Each ref In Array("B1", "D1", "F1", "H1", "J1","L1")
       Call AmendConditions(Range(ref), [Low_2], [High_2])
    Next ref
    
End Sub

Private Sub AmendConditions(cel As Range, low, high)
        With cel.Resize(100)
        'clear old condition
            .FormatConditions.Delete
        'add new condition
            .FormatConditions.AddColorScale ColorScaleType:=2
            .FormatConditions(.FormatConditions.Count).SetFirstPriority
            With .FormatConditions(1)
                .ColorScaleCriteria(1).Type = xlConditionValueLowestValue
                .ColorScaleCriteria(1).FormatColor.Color = low.Interior.Color
                .ColorScaleCriteria(2).Type = xlConditionValueHighestValue
                .ColorScaleCriteria(2).FormatColor.Color = high.Interior.Color
            End With
        End With
End Sub
run LoopRangesToFormat after amending colours in column P

Excel 2016 (Windows) 32 bit
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
1
1​
1​
100​
100​
1000​
1000​
2392​
2392​
7569​
7569​
6884​
6884​
Named range "Low_1" ---->
2
2​
2​
200​
200​
2000​
2000​
8074​
8074​
1008​
1008​
6046​
6046​
Named range "High_1" ---->
3
3​
3​
300​
300​
3000​
3000​
8143​
8143​
2423​
2423​
8966​
8966​
4
4​
4​
400​
400​
4000​
4000​
4052​
4052​
9871​
9871​
1323​
1323​
Named range "Low_2" ---->
5
5​
5​
500​
500​
5000​
5000​
2474​
2474​
737​
737​
5564​
5564​
Named range "High_2" ---->
6
6​
6​
600​
600​
6000​
6000​
5827​
5827​
3995​
3995​
4747​
4747​
7
7​
7​
700​
700​
7000​
7000​
7069​
7069​
3967​
3967​
9614​
9614​
8
8​
8​
800​
800​
8000​
8000​
4054​
4054​
9255​
9255​
3879​
3879​
9
9​
9​
900​
900​
9000​
9000​
6791​
6791​
6055​
6055​
4770​
4770​
10
10​
10​
1000​
1000​
10000​
10000​
4634​
4634​
2159​
2159​
8951​
8951​
11
11​
11​
1100​
1100​
11000​
11000​
459​
459​
6221​
6221​
5139​
5139​
12
12​
12​
1200​
1200​
12000​
12000​
3737​
3737​
5557​
5557​
9696​
9696​
13
13​
13​
1300​
1300​
13000​
13000​
4645​
4645​
5999​
5999​
3094​
3094​
14
14​
14​
1400​
1400​
14000​
14000​
4832​
4832​
440​
440​
1709​
1709​
15
15​
15​
1500​
1500​
15000​
15000​
4145​
4145​
7575​
7575​
3059​
3059​
Sheet: Sheet2
and again run LoopRangesToFormat after amending colours in column P

Excel 2016 (Windows) 32 bit
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
1
1​
1​
100​
100​
1000​
1000​
2392​
2392​
7569​
7569​
6884​
6884​
Named range "Low_1" ---->
2
2​
2​
200​
200​
2000​
2000​
8074​
8074​
1008​
1008​
6046​
6046​
Named range "High_1" ---->
3
3​
3​
300​
300​
3000​
3000​
8143​
8143​
2423​
2423​
8966​
8966​
4
4​
4​
400​
400​
4000​
4000​
4052​
4052​
9871​
9871​
1323​
1323​
Named range "Low_2" ---->
5
5​
5​
500​
500​
5000​
5000​
2474​
2474​
737​
737​
5564​
5564​
Named range "High_2" ---->
6
6​
6​
600​
600​
6000​
6000​
5827​
5827​
3995​
3995​
4747​
4747​
7
7​
7​
700​
700​
7000​
7000​
7069​
7069​
3967​
3967​
9614​
9614​
8
8​
8​
800​
800​
8000​
8000​
4054​
4054​
9255​
9255​
3879​
3879​
9
9​
9​
900​
900​
9000​
9000​
6791​
6791​
6055​
6055​
4770​
4770​
10
10​
10​
1000​
1000​
10000​
10000​
4634​
4634​
2159​
2159​
8951​
8951​
11
11​
11​
1100​
1100​
11000​
11000​
459​
459​
6221​
6221​
5139​
5139​
12
12​
12​
1200​
1200​
12000​
12000​
3737​
3737​
5557​
5557​
9696​
9696​
13
13​
13​
1300​
1300​
13000​
13000​
4645​
4645​
5999​
5999​
3094​
3094​
14
14​
14​
1400​
1400​
14000​
14000​
4832​
4832​
440​
440​
1709​
1709​
15
15​
15​
1500​
1500​
15000​
15000​
4145​
4145​
7575​
7575​
3059​
3059​
Sheet: Sheet2
 
Last edited:

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,343
Office Version
365
Platform
Windows
Thanks for the feedback
(y)
 

Watch MrExcel Video

Forum statistics

Threads
1,102,302
Messages
5,486,054
Members
407,529
Latest member
netojose

This Week's Hot Topics

Top