Multiple Ranges in Color Scale UDF

abc_xyz

New Member
Joined
Jan 12, 2022
Messages
47
Office Version
  1. 2016
Platform
  1. Windows
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?

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
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
You could just call it like this:

Excel Formula:
=Macro1((A2,C2, E2:G2))
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top