Data type error for Color Scale UDF

[abc_xyz]

I have created a VBA UDF to add 3 Color Scale formatting to a given range. The below code seems to be working when the value of Rng is hardcoded as "G5:J5" (with double quotes). Instead, I want it to use just G5:J5 so that I could drag it down.
Can anyone please help?

Function Macro1(Rng As String)

    Dim MyRng As Range
    Set MyRng = Range(Rng)
 
 
    MyRng.FormatConditions.AddColorScale ColorScaleType:=3
    MyRng.FormatConditions(MyRng.FormatConditions.Count).SetFirstPriority
    
    With MyRng.FormatConditions(1)
    
        With .ColorScaleCriteria(1)
            .Type = xlConditionValueLowestValue
            .FormatColor.Color = 7039480
        End With
        
        With .ColorScaleCriteria(2)
            .Type = xlConditionValuePercentile
            .Value = 50
            .FormatColor.Color = 8711167
        End With
        
        With .ColorScaleCriteria(3)
            .Type = xlConditionValueHighestValue
            .FormatColor.Color = 8109667
        End With
        
    End With
    
End Function

 

[abc_xyz]

You're welcome & thanks for the feedback.

Why doesn't it work if I pass the value for Rng as A5,C5,E5,G5?

 

[Fluff]

The function is expecting a single range, not multiple ranges.

 

[abc_xyz]

The function is expecting a single range, not multiple ranges.

Is there a way we could implement that in the above code?

 

[Fluff]

As this is a significantly different question, it needs a new thread. Thanks