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

 

[Fluff]

How about

Function Macro1(Rng As Range)


 
    Rng.FormatConditions.AddColorScale ColorScaleType:=3
    Rng.FormatConditions(MyRng.FormatConditions.Count).SetFirstPriority
    
    With Rng.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]

It works fine. But the cell on which the UDF is written shows #VALUE. Instead, can we add TURE or DONE?

How about

Function Macro1(Rng As Range)


 
    Rng.FormatConditions.AddColorScale ColorScaleType:=3
    Rng.FormatConditions(MyRng.FormatConditions.Count).SetFirstPriority
  
    With Rng.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

 

[Fluff]

Add this just before the End Sub

    Macro1 = "Done"

 

[abc_xyz]

Add this just before the End Sub

    Macro1 = "Done"

Does not work.

 

[Fluff]

In that case please post the code you are now using.

 

[abc_xyz]

Here..

Function NewMacro(Rng As Range)

    Rng.FormatConditions.Delete
    Rng.FormatConditions.AddColorScale ColorScaleType:=3
    Rng.FormatConditions(MyRng.FormatConditions.Count).SetFirstPriority
    
    With Rng.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
    
    NewMacro = "Done"
    
End Function

 

[Fluff]

I missed changing one of the MyRng variables it should be

    Rng.FormatConditions(Rng.FormatConditions.Count).SetFirstPriority

 

[abc_xyz]

It worked. Thanks!

I missed changing one of the MyRng variables it should be

    Rng.FormatConditions(Rng.FormatConditions.Count).SetFirstPriority

 

[Fluff]

You're welcome & thanks for the feedback.