The below code works fine if the a single range is provided, example: =Macro1(A2:F2). But does not with multiple ranges, example: =Macro1(A2,C2, E2:G2)
Is the a way around I could make it dynamic?
Is the a way around I could make it dynamic?
VBA Code:
Function Macro1(Rng As Range)
If Application.WorksheetFunction.Sum(Rng) <> 0 Then
'Color Scale 1
Rng.FormatConditions.Delete
Rng.FormatConditions.AddColorScale ColorScaleType:=3
Rng.FormatConditions(Rng.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
Else
'Color Scale 2
Rng.FormatConditions.Delete
Rng.FormatConditions.AddColorScale ColorScaleType:=3
Rng.FormatConditions(Rng.FormatConditions.Count).SetFirstPriority
With Rng.FormatConditions(1)
With .ColorScaleCriteria(1)
.Type = xlConditionValueLowestValue
.FormatColor.Color = 8109667
End With
With .ColorScaleCriteria(2)
.Type = xlConditionValuePercentile
.Value = 50
.FormatColor.Color = 8711167
End With
With .ColorScaleCriteria(3)
.Type = xlConditionValueHighestValue
.FormatColor.Color = 7039480
End With
End With
End If
Macro1 = "Done"
End Function