Automatically inserting shapes into cells based on cell value or conditional formatting?

[Grenamier]

Hello,

I have a simple grid where the rows are people and columns are weeks. There are formulas in the grid that count how many audits a given person has done in a given week and I've conditionally formatted the cells to turn green when the cell is >= 1. I've been asked specifically to put a red dot in each cell that's 0. Is there a way to do this without manually copying and pasting red circles over each cell? I imagine this can be done by looping and drawing in VBA, but I'd prefer as simple as solution as possible.

Thanks!

 

[gallen]

Does this help? You should be able to adapt as necessary. Sorry I couldn't do the loop etc as I'm off home now.

Sub addCircle(r As Range)
    Dim s As Shape
    
    'add shape to the top left of the cell with a size of 10,10
    Set s = Me.Shapes.AddShape(Type:=msoShapeOval, Left:=r.Left, Top:=r.Top, Width:=10, Height:=10)
    
    'Make it red
    s.Fill.ForeColor.RGB = RGB(255, 0, 0)
    
    
End Sub


Sub test()
    'Change 'B1' to any cell
    addCircle [B1]
End Sub

 

[Grenamier]

Thanks! It's not a big range so I'll just loop it and that'll work. Much appreciated.