How to apply one conditional formatting rule to several ranges?
Results 1 to 9 of 9

Thread: How to apply one conditional formatting rule to several ranges?

  1. #1
    New Member
    Join Date
    Mar 2018
    Location
    Gent, Belgium
    Posts
    47
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default How to apply one conditional formatting rule to several ranges?

    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 by Sam Hamels; Jul 9th, 2019 at 07:55 AM.

  2. #2
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,607
    Post Thanks / Like
    Mentioned
    62 Post(s)
    Tagged
    7 Thread(s)

    Default Re: How to apply one conditional formatting rule to several ranges?

    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

  3. #3
    New Member
    Join Date
    Mar 2018
    Location
    Gent, Belgium
    Posts
    47
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to apply one conditional formatting rule to several ranges?

    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 by Sam Hamels; Jul 9th, 2019 at 10:58 AM.

  4. #4
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,607
    Post Thanks / Like
    Mentioned
    62 Post(s)
    Tagged
    7 Thread(s)

    Default Re: How to apply one conditional formatting rule to several ranges?

    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 )

  5. #5
    New Member
    Join Date
    Mar 2018
    Location
    Gent, Belgium
    Posts
    47
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to apply one conditional formatting rule to several ranges?

    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.

  6. #6
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,607
    Post Thanks / Like
    Mentioned
    62 Post(s)
    Tagged
    7 Thread(s)

    Default Re: How to apply one conditional formatting rule to several ranges?

    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 ?

  7. #7
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,607
    Post Thanks / Like
    Mentioned
    62 Post(s)
    Tagged
    7 Thread(s)

    Default Re: How to apply one conditional formatting rule to several ranges?

    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 by Yongle; Jul 10th, 2019 at 04:59 AM.

  8. #8
    New Member
    Join Date
    Mar 2018
    Location
    Gent, Belgium
    Posts
    47
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to apply one conditional formatting rule to several ranges?

    Thanks works, nice!
    Thanks!

  9. #9
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,607
    Post Thanks / Like
    Mentioned
    62 Post(s)
    Tagged
    7 Thread(s)

    Default Re: How to apply one conditional formatting rule to several ranges?

    Thanks for the feedback

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •