ooitzechyi
09-22-2016, 09:01 PM
Hi,
I'm trying to get the shape count by color by referring the color code in another sheet ("Summary") and input the result to that sheet ("Summary"), but it seems like not functioning and only show result in first row.
In Sheet1, I have shapes with different colors and either group or ungroup.
In Sheet('Summary'), I have my RGB code in Col C, Col D, Col E (Row 2 - 5).
I want the result to be input to Col F according to the code in Col C - E.
For eg,
My reference: (Col C - Col E)
Row 2: 255 200 255
Row 3: 0 255 0
Row 4: 200 200 200
Row 5: 0 0 255
My Result: (Col F)
Row 2: 2
Row 3: 11
Row 4: 0
Row 5: 5
Now what I could get was on Row 2 = 0, Row 3 - 5 = ""
Private Sub Ignore_Click()
Dim sh As Sheet1
Dim shp As Shape
Dim shp2 As Shapes
Dim shprange As ShapeRange
CountShape = 0
CountChildShape = 0
'Sheet1.Shapes.SelectAll
Set SmClr = Application.ThisWorkbook.Sheets("Summary")
LRow1 = SmClr.Range("C" & Rows.Count).End(xlUp).Row + 1
LRow2 = SmClr.Range("D" & Rows.Count).End(xlUp).Row + 1
LRow3 = SmClr.Range("E" & Rows.Count).End(xlUp).Row + 1
LRowC = SmClr.Range("C" & LRow1)
LRowD = SmClr.Range("D" & LRow2)
LRowE = SmClr.Range("E" & LRow3)
For Each shp In Sheet1.Shapes
If shp.Type = msoGroup Then
Set shprange = shp.Ungroup
Set oMyGroup = shprange.Group
If shprange.Fill.ForeColor.RGB = RGB(LRowC, LRowD, LRowE) Then
CountChildShape = CountChildShape + 1
End If
End If
Next shp
For Each shp In Sheet1.Shapes
If shp.Type <> msoGroup Then
If shp.Fill.ForeColor.RGB = RGB(LRowC, LRowD, LRowE) Then
CountShape = CountShape + 1
End If
End If
Next shp
CntShp = CountShape + CountChildShape
LastRow = SmClr.Range("F" & Rows.Count).End(xlUp).Row + 1
Application.ThisWorkbook.Sheets("Summary").Range("F" & LastRow).Value = CntShp
End Sub
Thank you.
I'm trying to get the shape count by color by referring the color code in another sheet ("Summary") and input the result to that sheet ("Summary"), but it seems like not functioning and only show result in first row.
In Sheet1, I have shapes with different colors and either group or ungroup.
In Sheet('Summary'), I have my RGB code in Col C, Col D, Col E (Row 2 - 5).
I want the result to be input to Col F according to the code in Col C - E.
For eg,
My reference: (Col C - Col E)
Row 2: 255 200 255
Row 3: 0 255 0
Row 4: 200 200 200
Row 5: 0 0 255
My Result: (Col F)
Row 2: 2
Row 3: 11
Row 4: 0
Row 5: 5
Now what I could get was on Row 2 = 0, Row 3 - 5 = ""
Private Sub Ignore_Click()
Dim sh As Sheet1
Dim shp As Shape
Dim shp2 As Shapes
Dim shprange As ShapeRange
CountShape = 0
CountChildShape = 0
'Sheet1.Shapes.SelectAll
Set SmClr = Application.ThisWorkbook.Sheets("Summary")
LRow1 = SmClr.Range("C" & Rows.Count).End(xlUp).Row + 1
LRow2 = SmClr.Range("D" & Rows.Count).End(xlUp).Row + 1
LRow3 = SmClr.Range("E" & Rows.Count).End(xlUp).Row + 1
LRowC = SmClr.Range("C" & LRow1)
LRowD = SmClr.Range("D" & LRow2)
LRowE = SmClr.Range("E" & LRow3)
For Each shp In Sheet1.Shapes
If shp.Type = msoGroup Then
Set shprange = shp.Ungroup
Set oMyGroup = shprange.Group
If shprange.Fill.ForeColor.RGB = RGB(LRowC, LRowD, LRowE) Then
CountChildShape = CountChildShape + 1
End If
End If
Next shp
For Each shp In Sheet1.Shapes
If shp.Type <> msoGroup Then
If shp.Fill.ForeColor.RGB = RGB(LRowC, LRowD, LRowE) Then
CountShape = CountShape + 1
End If
End If
Next shp
CntShp = CountShape + CountChildShape
LastRow = SmClr.Range("F" & Rows.Count).End(xlUp).Row + 1
Application.ThisWorkbook.Sheets("Summary").Range("F" & LastRow).Value = CntShp
End Sub
Thank you.