How to apply one conditional formatting rule to several ranges?

Sam Hamels

New Member
Joined
Mar 20, 2018
Messages
49
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:

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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
 
Upvote 0
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:

obQhjkN.png


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:
Upvote 0
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 )
 
Upvote 0
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.
 
Upvote 0
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 ?
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top