How do I simplify this Code?

Hello, this code applies conditional formatting to data in 12 Excel columns. Each column is unrelated to the other column. How do I simply this code so it is not repeated 12x? Thank you.

This is a sample;

Sub Macro1()
'
' Macro1 Macro
' 

    Range("F2:F50").Select
    Range(Selection, Selection.End(xlUp)).Select
        
    Selection.FormatConditions.AddColorScale ColorScaleType:=3
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    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
    
    Range("H2:H50").Select
    Range(Selection, Selection.End(xlUp)).Select
       
    Selection.FormatConditions.AddColorScale ColorScaleType:=3
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    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
    
    
     Range("I2:I50").Select
    Range(Selection, Selection.End(xlUp)).Select
       
    Selection.FormatConditions.AddColorScale ColorScaleType:=3
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    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
    
    
    
    Range("J2:J50").Select
    Range(Selection, Selection.End(xlUp)).Select
       
    Selection.FormatConditions.AddColorScale ColorScaleType:=3
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    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
    
    Range("K2:K50").Select
    Range(Selection, Selection.End(xlUp)).Select
       
    Selection.FormatConditions.AddColorScale ColorScaleType:=3
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    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

Hi there,

See if the following code does what you need:

Option Explicit


Sub ApplyConditionalFormatting()

    Const sSHEET_NAME   As String = "Sheet1"

    Dim rCellsToFormat  As Range
    Dim wksTarget       As Worksheet
    Dim vaRanges        As Variant
    Dim vRange          As Variant
    Dim sRange          As String

    Set wksTarget = ThisWorkbook.Worksheets(sSHEET_NAME)

    vaRanges = Array("F2:F50", "H2:H50", "I2:I50", "J2:J50", "K2:K50")

    For Each vRange In vaRanges

        sRange = CStr(vRange)

        Set rCellsToFormat = wksTarget.Range(sRange)

        With rCellsToFormat

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

            With .FormatConditions(1)

                With .ColorScaleCriteria(1)

                    .Type = xlConditionValueLowestValue
                    .FormatColor.Color = 7039480
                    .FormatColor.TintAndShade = 0

                End With

                With .ColorScaleCriteria(2)

                    .Type = xlConditionValuePercentile
                    .Value = 50
                    .FormatColor.Color = 8711167
                    .FormatColor.TintAndShade = 0

                End With

                With .ColorScaleCriteria(3)

                    .Type = xlConditionValueHighestValue
                    .FormatColor.Color = 8109667
                    .FormatColor.TintAndShade = 0

                End With

            End With

        End With

    Next vRange

End Sub

The highlighted values may be altered to suit your requirements.


Hope this helps - please let me know how you get on.

Regards,

Greg M

Thanks Greg. I'm no developer so have to be honest..... getting that code to work on my XLS is a bit of a mystery. I kind of makes sense but getting it to work, well that's a whole other challenge..... If you had a spare 5minutes I could send you the XLS with the data Im trying to format..... but understand if you are busy.

Cheers.

Hi again,

Sure, no problem.

I'll send you my email address in a private message so that you can send it to me directly if you don't want to post all of the data here.

Regards,

Greg M