• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA to change shape formatting via another cell value


New Member
Hi all,

I found a lot of useful tips and tricks in this forum and was wondering whether someone could help me with shape formatting via VBA.
This is an example I found in this forum however I'm struggling to find a way to fix an issue I'm having. I need for the shapes to change its transparency via another cell. It works if that particular cell value is changed manually but it doesn't if that cell has a formula that links to another cell (please see attached). Am I missing something in the code? I need the transparency to change after A1 value and A2 value is changed.



Well-Known Member
Use the Worksheet_Calculate event instead:
Private Sub Worksheet_Calculate()
Dim sh As Shape
Dim myCell As Range

Set myCell = Me.Range("A2")
'Turn off screen updating temporarily
Application.ScreenUpdating = False
'Loop over all shapes in sheet
For Each sh In Me.Shapes
  sh.Fill.Transparency = myCell.Value
Next sh
Application.ScreenUpdating = True
End Sub