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.

 

[jdellasala]

Thanks for your insight.

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.


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


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.


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?

I'll also point out that the script is flawed in that the counter i is not declared - a known memory issue with VBA. My Visual Basic ALWAYS starts a macro with Option Explicit and so the Macro couldn't run as presented, so there's that too.

So no kidding, I think after 30+ years of working with Excel specifically, and at least another decade with the forerunners of Excel - Visicalc and Lotus 123 (heard of them?), I know that there needs to be a good reason for using VBA, and this ain't one of them!

If you look at my more recent post, you'll see it's even easier than I originally posted, so a LOT fewer steps, and I DOUBT it took less time to come up with the script than it took me to do the entire project.

There are times that VBA is needed, but that should be for when no other methods are available, especially considering the increased security risk it presents.

This particular forum seems to have a VBA fetish using it to solve problems that can be solved using other tools readily available, especially when it involves needing to do something that Power Query was actually designed to do for example.
Anyway, do what you want.

 

[edss]

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.

Thanks. Not intended to use VBA but if that's the only I got no choice.

May I know how do I use the code? Do I need to change anything before applying?

 

[Fluff]

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.

Except that is not a solution as it doesn't work.

 

[Fluff]

Thanks. Not intended to use VBA but if that's the only I got no choice.

Unfortunately that is the only way to do it, other than formatting each column individually.

 

[jdellasala]

Except that is not a solution as it doesn't work.

I disagree. Show me how it doesn't work.

 

[Fluff]

You could just as easily looked at your posts to realise that the applied to range is not per column.

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J
1
2	63	308	456	489	557	612	345	212	739	535
3	120	277	883	79	762	842	849	342	430	594
4	941	763	162	345	847	746	419	960	689	245
5	267	842	689	678	680	7	720	461	142	46
6	673	894	734	528	309	260	221	475	737	252
7	367	833	503	35	329	12	937	891	579	779
8	856	510	541	581	680	11	388	629	785	165
9	258	354	331	644	13	637	18	191	721	538
10	663	783	613	598	299	387	581	447	950	604
11	414	45	823	797	837	468	6	456	845	484
12	787	794	661	891	24	347	467	505	229	881
13	935	75	591	662	849	123	488	769	586	183
14	37	46	535	541	1000	669	964	547	407	978
15	997	812	310	476	152	894	711	609	324	712
16	689	539	627	773	201	272	743	835	640	348
17	545	245	78	786	587	999	773	36	545	973
18	324	771	838	46	324	110	218	40	128	848
19	45	611	960	458	985	86	978	826	737	266
20	511	172	621	843	535	197	572	465	69	547
21	733	90	458	320	394	20	608	234	435	2
Main

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B:J	Other Type	Color scale		NO
A:A	Other Type	Color scale		NO

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J
1
2	63	308	456	489	557	612	345	212	739	535
3	120	277	883	79	762	842	849	342	430	594
4	941	763	162	345	847	746	419	960	689	245
5	267	842	689	678	680	7	720	461	142	46
6	673	894	734	528	309	260	221	475	737	252
7	367	833	503	35	329	12	937	891	579	779
8	856	510	541	581	680	11	388	629	785	165
9	258	354	331	644	13	637	18	191	721	538
10	663	783	613	598	299	387	581	447	950	604
11	414	45	823	797	837	468	1000000	456	845	484
12	787	794	661	891	24	347	467	505	229	881
13	935	75	591	662	849	123	488	769	586	183
14	37	46	535	541	1000	669	964	547	407	978
15	997	812	310	476	152	894	711	609	324	712
16	689	539	627	773	201	272	743	835	640	348
17	545	245	78	786	587	999	773	36	545	973
18	324	771	838	46	324	110	218	40	128	848
19	45	611	960	458	985	86	978	826	737	266
20	511	172	621	843	535	197	572	465	69	547
21	733	90	458	320	394	20	608	234	435	2
Main

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B:J	Other Type	Color scale		NO
A:A	Other Type	Color scale		NO

 

[edss]

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.

Just figured out how to use it. May I know how do I change the colorscale type or the colors or reference to it?

 

[frabulator]

Just figured out how to use it. May I know how do I change the colorscale type or the colors or reference to it?

Glad to see my broken, un-usable code worked for someone other than me

You can change the color schemes with this part of the code:

        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

A great way to see how things work when using VBA is with the recorder. By using the Record Macro you can record yourself doing a series of task and see how Excel operates your process in VBA.

-EDIT-
A quick tutorial on this can be found here.
This link is better
You can open the VBA Editor to see how this works by pressing Alt+F11
-EDIT-

These are the results from my recording. Just replace the above section whichever variation you need. The code below shows how to change the color of the current selection into the first four presets of conditional formatting. You can do this for any of the presets, or record your own and see how to do custom coloring.

(note, because this goes off of the selection, and the above code goes off of a range, you will need to replace 'Selection.' with the defined 'rng.' to have the code work correctly)

Sub ConFormatting1()
'
' Macro3 Macro
'

'
    Selection.FormatConditions(1).ColorScaleCriteria(1).Type = _
        xlConditionValueLowestValue
    With Selection.FormatConditions(1).ColorScaleCriteria(1).FormatColor
        .Color = 7039480
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).ColorScaleCriteria(2).Type = _
        xlConditionValuePercentile
    Selection.FormatConditions(1).ColorScaleCriteria(2).Value = 50
    With Selection.FormatConditions(1).ColorScaleCriteria(2).FormatColor
        .Color = 8711167
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).ColorScaleCriteria(3).Type = _
        xlConditionValueHighestValue
    With Selection.FormatConditions(1).ColorScaleCriteria(3).FormatColor
        .Color = 8109667
        .TintAndShade = 0
    End With
End Sub
Sub ConFormatting2()
'
' Macro4 Macro
'

'
    Selection.FormatConditions(1).ColorScaleCriteria(1).Type = _
        xlConditionValueLowestValue
    With Selection.FormatConditions(1).ColorScaleCriteria(1).FormatColor
        .Color = 8109667
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).ColorScaleCriteria(2).Type = _
        xlConditionValuePercentile
    Selection.FormatConditions(1).ColorScaleCriteria(2).Value = 50
    With Selection.FormatConditions(1).ColorScaleCriteria(2).FormatColor
        .Color = 8711167
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).ColorScaleCriteria(3).Type = _
        xlConditionValueHighestValue
    With Selection.FormatConditions(1).ColorScaleCriteria(3).FormatColor
        .Color = 7039480
        .TintAndShade = 0
    End With
End Sub
Sub ConFormatting3()
'
' Macro5 Macro
'

'
    Selection.FormatConditions(1).ColorScaleCriteria(1).Type = _
        xlConditionValueLowestValue
    With Selection.FormatConditions(1).ColorScaleCriteria(1).FormatColor
        .Color = 7039480
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).ColorScaleCriteria(2).Type = _
        xlConditionValuePercentile
    Selection.FormatConditions(1).ColorScaleCriteria(2).Value = 50
    With Selection.FormatConditions(1).ColorScaleCriteria(2).FormatColor
        .Color = 16776444
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).ColorScaleCriteria(3).Type = _
        xlConditionValueHighestValue
    With Selection.FormatConditions(1).ColorScaleCriteria(3).FormatColor
        .Color = 8109667
        .TintAndShade = 0
    End With
End Sub
Sub ConFormatting4()
'
' Macro6 Macro
'

'
    Selection.FormatConditions(1).ColorScaleCriteria(1).Type = _
        xlConditionValueLowestValue
    With Selection.FormatConditions(1).ColorScaleCriteria(1).FormatColor
        .Color = 8109667
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).ColorScaleCriteria(2).Type = _
        xlConditionValuePercentile
    Selection.FormatConditions(1).ColorScaleCriteria(2).Value = 50
    With Selection.FormatConditions(1).ColorScaleCriteria(2).FormatColor
        .Color = 16776444
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).ColorScaleCriteria(3).Type = _
        xlConditionValueHighestValue
    With Selection.FormatConditions(1).ColorScaleCriteria(3).FormatColor
        .Color = 7039480
        .TintAndShade = 0
    End With
End Sub

 

[edss]

Glad to see my broken, un-usable code worked for someone other than me

You can change the color schemes with this part of the code:

        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

A great way to see how things work when using VBA is with the recorder. By using the Record Macro you can record yourself doing a series of task and see how Excel operates your process in VBA.

-EDIT-
A quick tutorial on this can be found here.
This link is better
You can open the VBA Editor to see how this works by pressing Alt+F11
-EDIT-

These are the results from my recording. Just replace the above section whichever variation you need. The code below shows how to change the color of the current selection into the first four presets of conditional formatting. You can do this for any of the presets, or record your own and see how to do custom coloring.

(note, because this goes off of the selection, and the above code goes off of a range, you will need to replace 'Selection.' with the defined 'rng.' to have the code work correctly)

Sub ConFormatting1()
'
' Macro3 Macro
'

'
    Selection.FormatConditions(1).ColorScaleCriteria(1).Type = _
        xlConditionValueLowestValue
    With Selection.FormatConditions(1).ColorScaleCriteria(1).FormatColor
        .Color = 7039480
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).ColorScaleCriteria(2).Type = _
        xlConditionValuePercentile
    Selection.FormatConditions(1).ColorScaleCriteria(2).Value = 50
    With Selection.FormatConditions(1).ColorScaleCriteria(2).FormatColor
        .Color = 8711167
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).ColorScaleCriteria(3).Type = _
        xlConditionValueHighestValue
    With Selection.FormatConditions(1).ColorScaleCriteria(3).FormatColor
        .Color = 8109667
        .TintAndShade = 0
    End With
End Sub
Sub ConFormatting2()
'
' Macro4 Macro
'

'
    Selection.FormatConditions(1).ColorScaleCriteria(1).Type = _
        xlConditionValueLowestValue
    With Selection.FormatConditions(1).ColorScaleCriteria(1).FormatColor
        .Color = 8109667
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).ColorScaleCriteria(2).Type = _
        xlConditionValuePercentile
    Selection.FormatConditions(1).ColorScaleCriteria(2).Value = 50
    With Selection.FormatConditions(1).ColorScaleCriteria(2).FormatColor
        .Color = 8711167
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).ColorScaleCriteria(3).Type = _
        xlConditionValueHighestValue
    With Selection.FormatConditions(1).ColorScaleCriteria(3).FormatColor
        .Color = 7039480
        .TintAndShade = 0
    End With
End Sub
Sub ConFormatting3()
'
' Macro5 Macro
'

'
    Selection.FormatConditions(1).ColorScaleCriteria(1).Type = _
        xlConditionValueLowestValue
    With Selection.FormatConditions(1).ColorScaleCriteria(1).FormatColor
        .Color = 7039480
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).ColorScaleCriteria(2).Type = _
        xlConditionValuePercentile
    Selection.FormatConditions(1).ColorScaleCriteria(2).Value = 50
    With Selection.FormatConditions(1).ColorScaleCriteria(2).FormatColor
        .Color = 16776444
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).ColorScaleCriteria(3).Type = _
        xlConditionValueHighestValue
    With Selection.FormatConditions(1).ColorScaleCriteria(3).FormatColor
        .Color = 8109667
        .TintAndShade = 0
    End With
End Sub
Sub ConFormatting4()
'
' Macro6 Macro
'

'
    Selection.FormatConditions(1).ColorScaleCriteria(1).Type = _
        xlConditionValueLowestValue
    With Selection.FormatConditions(1).ColorScaleCriteria(1).FormatColor
        .Color = 8109667
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).ColorScaleCriteria(2).Type = _
        xlConditionValuePercentile
    Selection.FormatConditions(1).ColorScaleCriteria(2).Value = 50
    With Selection.FormatConditions(1).ColorScaleCriteria(2).FormatColor
        .Color = 16776444
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).ColorScaleCriteria(3).Type = _
        xlConditionValueHighestValue
    With Selection.FormatConditions(1).ColorScaleCriteria(3).FormatColor
        .Color = 7039480
        .TintAndShade = 0
    End With
End Sub

Thanks. Let me study and try it out first.

In your first VBA code I suppose I should change the red numbers? (sorry new to here don't even know how to copy your VBA codes in my post.)

The second VBA already have the 4 pre-set colors which is nice and convenient

 

[edss]

Glad to see my broken, un-usable code worked for someone other than me

You can change the color schemes with this part of the code:

        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

A great way to see how things work when using VBA is with the recorder. By using the Record Macro you can record yourself doing a series of task and see how Excel operates your process in VBA.

-EDIT-
A quick tutorial on this can be found here.
This link is better
You can open the VBA Editor to see how this works by pressing Alt+F11
-EDIT-

These are the results from my recording. Just replace the above section whichever variation you need. The code below shows how to change the color of the current selection into the first four presets of conditional formatting. You can do this for any of the presets, or record your own and see how to do custom coloring.

(note, because this goes off of the selection, and the above code goes off of a range, you will need to replace 'Selection.' with the defined 'rng.' to have the code work correctly)

Sub ConFormatting1()
'
' Macro3 Macro
'

'
    Selection.FormatConditions(1).ColorScaleCriteria(1).Type = _
        xlConditionValueLowestValue
    With Selection.FormatConditions(1).ColorScaleCriteria(1).FormatColor
        .Color = 7039480
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).ColorScaleCriteria(2).Type = _
        xlConditionValuePercentile
    Selection.FormatConditions(1).ColorScaleCriteria(2).Value = 50
    With Selection.FormatConditions(1).ColorScaleCriteria(2).FormatColor
        .Color = 8711167
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).ColorScaleCriteria(3).Type = _
        xlConditionValueHighestValue
    With Selection.FormatConditions(1).ColorScaleCriteria(3).FormatColor
        .Color = 8109667
        .TintAndShade = 0
    End With
End Sub
Sub ConFormatting2()
'
' Macro4 Macro
'

'
    Selection.FormatConditions(1).ColorScaleCriteria(1).Type = _
        xlConditionValueLowestValue
    With Selection.FormatConditions(1).ColorScaleCriteria(1).FormatColor
        .Color = 8109667
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).ColorScaleCriteria(2).Type = _
        xlConditionValuePercentile
    Selection.FormatConditions(1).ColorScaleCriteria(2).Value = 50
    With Selection.FormatConditions(1).ColorScaleCriteria(2).FormatColor
        .Color = 8711167
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).ColorScaleCriteria(3).Type = _
        xlConditionValueHighestValue
    With Selection.FormatConditions(1).ColorScaleCriteria(3).FormatColor
        .Color = 7039480
        .TintAndShade = 0
    End With
End Sub
Sub ConFormatting3()
'
' Macro5 Macro
'

'
    Selection.FormatConditions(1).ColorScaleCriteria(1).Type = _
        xlConditionValueLowestValue
    With Selection.FormatConditions(1).ColorScaleCriteria(1).FormatColor
        .Color = 7039480
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).ColorScaleCriteria(2).Type = _
        xlConditionValuePercentile
    Selection.FormatConditions(1).ColorScaleCriteria(2).Value = 50
    With Selection.FormatConditions(1).ColorScaleCriteria(2).FormatColor
        .Color = 16776444
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).ColorScaleCriteria(3).Type = _
        xlConditionValueHighestValue
    With Selection.FormatConditions(1).ColorScaleCriteria(3).FormatColor
        .Color = 8109667
        .TintAndShade = 0
    End With
End Sub
Sub ConFormatting4()
'
' Macro6 Macro
'

'
    Selection.FormatConditions(1).ColorScaleCriteria(1).Type = _
        xlConditionValueLowestValue
    With Selection.FormatConditions(1).ColorScaleCriteria(1).FormatColor
        .Color = 8109667
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).ColorScaleCriteria(2).Type = _
        xlConditionValuePercentile
    Selection.FormatConditions(1).ColorScaleCriteria(2).Value = 50
    With Selection.FormatConditions(1).ColorScaleCriteria(2).FormatColor
        .Color = 16776444
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).ColorScaleCriteria(3).Type = _
        xlConditionValueHighestValue
    With Selection.FormatConditions(1).ColorScaleCriteria(3).FormatColor
        .Color = 7039480
        .TintAndShade = 0
    End With
End Sub

For some reason the second VBA is not working. I have already replaced 'Selection.' with the defined 'rng.' or I misunderstand something here.