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.

 

[frabulator]

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

What line is it stopping at and what error is it giving you?

Please post the full code you are using as it will help us better understand if there is an issue.

Code can be put inside a code bracket at the top of the reply window with the “</>” icon.

 

[frabulator]

SORRY! I see the problem now.

You actually need to keep the following two lines in.

        Selection.FormatConditions.AddColorScale ColorScaleType:=3
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

I have adjusted the code so all you have to do is add in which ever section you want. What I did was change select 'rng.' so you can keep the 'Selection.'. I didnt think about about that yesterday. It'll make copy/pasting smoother.

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.Select
       
       
        Selection.FormatConditions.AddColorScale ColorScaleType:=3
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
       
       
   'this is the third conditional formatting option I think?
   'just replace this area with whatever formatting you need
   '--------------------------------------------------------------------
 
 
    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
   
   
    '--------------------------------------------------------------------
   
   
    Next
   
   'this selects the original selection back
   Dim rng2 As Range
   Set rng2 = Range(Cells(Sr, Sc), Cells(Er, Ec))
   rng2.Select
   
 


End Sub

 

[aminlak]

Thanks and really appreciate it guys!
These solutions solved my problem well.
I searched a lot on the internet and finally found my answer here.