Hi,
I'm new with VB. Pls bare with me.
I using VB excel and trying to get the number of shapes with same color but I'm stuck.
I had assigned a macro to insert shape with fixed color selection (where there will be more than 100+ of shapes with some fixed colors in my excel sheet),
and I need to get the summary of total of shapes with same color. For example,
I have 3 red shapes, 3 green shapes and 2 yellow shapes, I wish to get the result as
Cell (2,1): 3 (Red)
Cell (3,1): 3 (Green)
Cell (4,1): 2 (Yellow)
'Code to get the shape
Private Sub Green_Click()
Set myDocument = Worksheets(1)
myDocument.Shapes.AddShape(msoShapeRectangle, _
90, 90, 90, 50).Select
With Selection.ShapeRange.Fill
.ForeColor.RGB = RGB(0, 300, 0)
.BackColor.RGB = RGB(0, 300, 0)
.TwoColorGradient msoGradientHorizontal, 1
End With
With Selection.ShapeRange.Line
.ForeColor.RGB = RGB(0, 300, 0)
.BackColor.RGB = RGB(0, 300, 0)
End With
With SelectShapeColor
Unload Me
End With
End Sub
'Code for Calc Number of Shape
Sub CalcShape()
Dim sh As Sheet1
Dim shp As Shape
Dim a As Long
Dim b As Long
Dim CountShape As Long
For Each shp In Sheet1.Shapes
CountShape = 0
If shp.Fill.ForeColor.RGB = RGB(0, 300, 0) Then
Sheet1.Cells(2, 1) = CountShape + 1
End If
Next shp
End Sub
There is no error shows and the result I got is "1"
I'm not sure where goes wrong.
Pls helps~
Thanks
Bookmarks