Replace cell value with image

Question

I have an Excel file with a table containing values O and G. I want to replace O with an orange icon and G with a green icon

How do I read each cell for value O and G and replace them with their respective image?

Private Sub CommandButton1_Click()

For Each c In Worksheets("Summary (2)").Range("A1:D10")
    If c.Value = 0 Then
        c.Value = Orange
    ElseIf c.Value = G Then
        c.Value = "Green"
    Else
        c.Value = ""
    End If
Next c
End Sub

Have you considered using conditional formatting? You could assign the numbers 1,2,3 to O,G,R and use "Icon Set" in conditional formatting. — Sun, Jun 15 '16 at 07:29
Since your Loop Looks ok, it has to be the inserting of the Image which makes you Problems... If this is the case you should really reconsider your Question, and ask specifically for that. There are also some answers to this Problems on SO -> http://stackoverflow.com/questions/12936646/how-to-insert-a-picture-into-excel-at-a-specified-cell-position-with-vba — Doktor OSwaldo, Jun 15 '16 at 08:14
Problem I see is that you are looking for 0 (zero), but your cell value is O (letter O) - try changing the lines `c.Value = 0` and `c.Value = G` to `c.Value = "O"` and `c.Value = "G"`. Also where are you getting the icons from? — Dawid SA Tokyo, Jun 15 '16 at 08:34

score 0 · Answer 1 · answered Jun 15 '16 at 09:42

This is how you do it,

Private Sub CommandButton1_Click()   

Application.CopyObjectsWithCells = True
For Each c In Worksheets("Sector Summary (2)").Range("A1:H100")
 If c.Value = "O" Then
     Sheets("master").Cells(1, 2).Copy
     c.Select
     ActiveSheet.Paste

 ElseIf c.Value = "G" Then
     Sheets("master").Cells(2, 2).Copy
     c.Select
     ActiveSheet.Paste

 ElseIf c.Value = "R" Then
     Sheets("master").Cells(3, 2).Copy
     c.Select
     ActiveSheet.Paste
 Else
      c.Value = c.Value
 End If
Next c
End Sub

Replace cell value with image

1 Answers1