CODEWITHSUNDEEP
excelexcel-2010vba
orson
orson

Reputation: 97

Changing colors of a group of shapes without selecting (fill & text)

With regard to the following code:

Sub Macro1 ()
   With ActiveSheet.Shapes.Range(Array("MyShapeGroup")) 
     .Fill.ForeColor.RGB = cPurp  ''cPurp is global constant
     .TextFrame.Characters.Font.ColorIndex = 2
   End With
End Sub

I can execute the macro with only the change to the fill color, however, when I add in the text color change I get an Application-defined or object-defined error.

So I tried this:

Sub Macro1()
    With ActiveSheet.Shapes.Range(Array("MyShapeGroup")).ShapeRange
       .Fill.ForeColor.RGB = cPurp
       .TextFrame.Characters.Font.ColorIndex = 2
    End With
End Sub

Which throws an 'Object doesn't support this property or method' on the With line.

Also tried this:

Sub Macro1()
    With ActiveSheet.Shapes.Range(Array("MyShapeGroup"))
       .Fill.ForeColor.RGB = cPurp
       .ShapeRange.TextFrame.Characters.Font.ColorIndex = 2
    End With
End Sub

Which throws another 'Object doesn't support this property or method' on the .ShapeRange line.

Also tried this:

Sub Macro1 ()
    ActiveSheet.Shapes.Range(Array("MyShapeGroup")).Select
    With Selection.ShapeRange
        .Fill.ForeColor.RGB = cPurp ''cPurp is global constant
        .TextFrame.Characters.Font.ColorIndex = 2
    End With
End Sub

How can I efficiently do both the fill color change and the text color change without using a select?

Updated with working method (tested in and Excel 2010 & Excel in Office365):

Option Explicit

Sub test()
        With ActiveSheet.Shapes.Range(Array("MyShapeGroup"))
            .Fill.ForeColor.RGB = RGB(255, 255, 0)
            .TextFrame2.TextRange.Font.Fill.ForeColor.RGB = RGB(0, 255, 255)
        End With
End Sub

Upvotes: 0

Views: 4069

Answers (2)

Guido Hoogslag
Guido Hoogslag

Reputation: 11

I am no good at VBA, but I think this worked for me!

Sub test()
    Dim shp As Shape
    For Each shp In ActiveSheet.Shapes("MyShapeGroup").GroupItems
        shp.Fill.ForeColor.RGB = RGB(255, 255, 0)
        shp.TextFrame.Characters.Font.Color = vbWhite
    Next
End Sub

Upvotes: 1

Raystafarian
Raystafarian

Reputation: 3022

Try using the TextFrame2.TextRange property instead:

Option Explicit

Sub test()
    Dim shp As Shape
        For Each shp In ActiveSheet.Shapes.Range(Array("MyShapeGroup"))
            shp.Fill.ForeColor.RGB = RGB(255, 255, 0)
            shp.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = RGB(0, 255, 255)
        Next
End Sub

Upvotes: 2

Related Questions