locked
Use IconSet without conditional formatting RRS feed

  • Question

  • Thank you for taking the time to read my question.

    I'm wondering if there is a way to apply an IconSet without having conditional formatting using VBA. I just want to put a black circle or a green circle in a cell if an adjacent cell has a specific string in it.

    Any help would be great.

    Thanks,
    Brad

    Wednesday, October 11, 2017 8:50 PM

Answers

  • You can have both existing cell text and the fake icon...
    Note:  chr(110) is "n", the 14th character in the English alphabet.
    (lower case)
    '---
    Sub PrettyCircleR1()
     If VBA.Right(ActiveCell.Value, 1) <> VBA.Chr$(110) Then
       ActiveCell.Value = ActiveCell.Value & VBA.Chr$(110)
     End If
     With ActiveCell.Characters(VBA.Len(ActiveCell.Value), 1).Font
      .Name = "Marlett"
      .Color = vbGreen
     End With
    End Sub

    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)



    • Edited by James Cone Sunday, October 15, 2017 6:57 PM add image
    • Proposed as answer by Terry Xu - MSFT Monday, October 16, 2017 1:12 AM
    • Marked as answer by mbrad Monday, October 16, 2017 12:12 PM
    Friday, October 13, 2017 2:39 PM

All replies

  • Hi,

    To enable us to help you better, you need to provide as many details as you can about your issue and what you want to do. 

    for example...
    When is a black or green circle put. What does make its event?
    What is a cell where pug colored circle is put? How can we determine?
    When a specific string is determined?  Where is its value?

    Please make it clear and concrete what you want to do.

    Regards,

    Ashidacchi


    • Edited by Ashidacchi Thursday, October 12, 2017 1:22 AM
    Thursday, October 12, 2017 1:22 AM
  • Hi mbrad,

    As far as I know, the IconSet could only be used in conditional formatting.

    I'm wondering why do you want to set the black circle for the cell. If you want to mark a cell, what about setting background color or setting borders?

    Best Regards,

    Terry


    MSDN Community Support Please remember to click &quot;Mark as Answer&quot; the responses that resolved your issue, and to click &quot;Unmark as Answer&quot; if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, October 12, 2017 9:04 AM
  • Hi,

    Thank you for your replies.

    what I'd like to do is:

    if instr(1,tString,"IsMS",vbTextCompare) > 0 then
    show black dot
    elseif instr(1,tString,"IsWk",vbTextCompare) > 0 then
    show blue dot
    else
    show nothing
    end if

    I'm making kind of a MS Project type spreadsheet and I'd like to show when a row is a milestone by putting a black circle in the task number cell.

    I don't mind the idea of the black border, but thought the dot would look nicer. I thought of using shapes but if I reorder the rows, I have to also then move the shapes.

    Any other suggestions welcome.

    Brad

    Thursday, October 12, 2017 12:22 PM
  • Sub PrettyCircle()
     ActiveCell.Font.Name = "Marlett"
     ActiveCell.Value = VBA.Chr$(110)
     ActiveCell.Font.Color = vbBlue
    End Sub
    Friday, October 13, 2017 12:52 PM
  • Hi James,

    a very interesting approach. I do have text in the cell that I want to add the icon to, but maybe I can always trim out this character if it exists when I need to search through the column.

    Brad

    Friday, October 13, 2017 2:15 PM
  • You can have both existing cell text and the fake icon...
    Note:  chr(110) is "n", the 14th character in the English alphabet.
    (lower case)
    '---
    Sub PrettyCircleR1()
     If VBA.Right(ActiveCell.Value, 1) <> VBA.Chr$(110) Then
       ActiveCell.Value = ActiveCell.Value & VBA.Chr$(110)
     End If
     With ActiveCell.Characters(VBA.Len(ActiveCell.Value), 1).Font
      .Name = "Marlett"
      .Color = vbGreen
     End With
    End Sub

    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)



    • Edited by James Cone Sunday, October 15, 2017 6:57 PM add image
    • Proposed as answer by Terry Xu - MSFT Monday, October 16, 2017 1:12 AM
    • Marked as answer by mbrad Monday, October 16, 2017 12:12 PM
    Friday, October 13, 2017 2:39 PM