Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    circle a list numbers

    hi all,

    i have a numbered list and i want to circle & color the numbers, any ideas.

    thanks.

    tia
    dubdub
    TIA
    dubdub

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    If the numbers are "simple" such as 1, 2, 3, etc., there are encircled number from 1 - 20 in the insert | symbols option (try Wingdings). You can then select those inserted cells and change the font color. Any numbers more complicated don't fit this scheme. Not sure if there's another option.

  3. #3
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks kweaver

    what i was able to find is up to 10 and i need it for more than 20.
    TIA
    dubdub

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    You can use the circle object and edit the text to add the numbers desired. That gives you the complete control over size and color of the circle, the number and inside the circle.

    Steve

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    DubDub,

    To elaborate on Steve's suggestion you can enter a cell reference in the Edit Text Box. Then place the number in the actual cell.Excel Circles.JPGNote the formula bar as cell A4 is selected. In the other cells I have aligned the text to be centered in both directions to hide the number behind the circle, you could also change the font color to white to accomplish the same effect. The only problem is that you have to edit each circle to put its cell reference in as they do not adjust when you copy the shape. You'll also want to set the shape to move but NOT size with the cells. Hope this helps.

    Note: there isn't a circle shape. Use the Oval and hold the Shift key as you size to get a circle.
    Last edited by RetiredGeek; 2012-02-06 at 07:24.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #6
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts
    thanks to you all,

    i found he down code in one of the sites, the only problem the code form an ellipse shape instead of a circle, i would appreciate any help to make it do that.

    Private Sub Worksheet_Calculate()
    Application.ScreenUpdating = False
    Dim cell As Range, CircleRange As Range
    Dim oldShape As Shape, newShape As Shape
    Dim iCount%
    iCount = 0
    Set CircleRange = Range("A12:A" & Cells(Rows.Count, 6).End(xlUp).Row)

    For Each oldShape In ActiveSheet.Shapes
    If oldShape.Name Like "GreaterThanThree_*" Then oldShape.Delete
    Next

    For Each cell In CircleRange
    With cell
    If .Value >= 1 Then
    Set newShape = ActiveSheet.Shapes.AddShape(msoShapeOval, .Left - 2, .Top - 2, .Width + 4, .Height + 4)
    newShape.Fill.Visible = msoFalse
    newShape.Line.ForeColor.SchemeColor = 10
    newShape.Line.Weight = 1.25
    iCount = iCount + 1
    newShape.Name = "GreaterThanOne_" & iCount
    End If
    End With
    Next cell

    Set newShape = Nothing
    Set CircleRange = Nothing
    Application.ScreenUpdating = True
    End Sub
    Last edited by dubdub; 2012-02-07 at 08:27.
    TIA
    dubdub

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Change the height and width to be the same. If you want it to be based on the height of the cell then use:

    Set newShape = ActiveSheet.Shapes.AddShape(msoShapeOval, .Left - 2, .Top - 2, .Height , .Height)

    If you have a particular diameter in mind, you can use that directly. Note that the ".left-2" is the left tangent to the circle, and ".top-2" is the top tangent. Adjust these as neccessary
    br>Steve

  8. #8
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts
    thanks Steve,

    i will give it a try.
    TIA
    dubdub

  9. #9
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    All good ideas. The numbers from 1-20 are in normal text (sorry).

    NormalText.jpg

Posting Permissions

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