VBA getting multiple gradient Color from another shape color

Question

currently I figured out how to add multiple gradient stops with the code below. However, this way, I have to put RGB number in the code.

What I wanted to do is to get the color from another shape and put that color into the gradient stops instead of putting the RGB number.

I tried these lines but they don't work, any idea how to make it work? Thank you!

Function Get_Color(Index As Long) As Long
Get_Color = ActiveSheet.Shapes.Range(Array("Fill")).Fill.ForeColor
End Function

Sub gradients() 

Dim Point_Index As Long

 ActiveSheet.Shapes("Sauqre1").Select
 With ActiveSheet.Shapes("Sauqre1").Fill
 .ForeColor.RGB = RGB(0, 128, 128) 
 .OneColorGradient msoGradientHorizontal, 1, 1 
 .GradientStops.Insert RGB(255, 0, 0), 0.25 *This way work ok
 *But I want to get color from another shape, I tried these 3 lines, none of them work so far.
.GradientStops.Insert RGB = Get_Color(Point_Index) 
.GradientStops.Insert.RGB = Get_Color(Point_Index)
.GradientStops.Insert.Color.RGB = Get_Color(Point_Index)
 End With 
End Sub

Алексей Р · Accepted Answer · 2021-01-27 14:57:06Z

1

Function Get_Color(ShapeName As Variant) As Long
    Get_Color = ActiveSheet.Shapes(ShapeName).Fill.ForeColor
End Function

Sub gradients()
    Dim Point_Index As Variant ' may be name of shape or index of shape
    
    Point_Index = "OtherShape" ' or 2 if index of "OtherShape" is 2
    
    With ActiveSheet.Shapes("Sauqre1").Fill
       .ForeColor.RGB = RGB(0, 128, 128)
       .OneColorGradient msoGradientHorizontal, 1, 1
       .GradientStops.Insert Get_Color(Point_Index), 0.25
    End With
End Sub

answered Jan 27, 2021 at 14:57

Алексей Р

7,6272 gold badges9 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

CeeCee Over a year ago

OMG! YESS, this magical, thank you so much.

CeeCee Over a year ago

May I ask one more question please if I wanted to add .TintAndShade below this line, is that doable? For example, .GradientStops.Insert Get_Color(Point_Index), 0 .GradientStops.TintAndShade = -0.4

Алексей Р Over a year ago

Yes, e.g. .ForeColor.TintAndShade = 0.4

Collectives™ on Stack Overflow

VBA getting multiple gradient Color from another shape color

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related