+ Reply to Thread
Results 1 to 2 of 2

VB Excel, Count shapes with same color

  1. #1
    Registered User
    Join Date
    09-05-2016
    Location
    Malaysia
    MS-Off Ver
    2013
    Posts
    3

    VB Excel, Count shapes with same color

    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

  2. #2
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,639

    Re: VB Excel, Count shapes with same color

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

    Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here


    Try it like this.....
    Please Login or Register  to view this content.
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Color of shapes from cell content
    By SIMBAtheCAT in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-03-2014, 03:23 PM
  2. color coding shapes
    By deanusa in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-04-2014, 04:01 PM
  3. [SOLVED] Change color of text for a group of shapes
    By emiliekatherine in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-27-2012, 08:43 AM
  4. Help with loop to fill color of shapes (US map)
    By harleyberger in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-28-2012, 01:50 PM
  5. Color a shape of a group of shapes
    By lgiannetti in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-13-2011, 09:18 AM
  6. Changing Shapes line color using VBA
    By ABabeNChrist in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 11-23-2010, 10:58 AM
  7. [SOLVED] VBA Help with changing shape color for selected shapes only
    By Nimrod in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 04-29-2005, 09:06 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1