VBA Code Doesn't Recognize Cell Formular

ddddhhhh

New Member
Joined
Dec 29, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
I am trying to get a shape to appear and disappear based on the result of a formula, but I am having trouble making this dynamic. The code below works when I hard code the value into cell U2, but when I try to have this formula =IF(V2<=Dashboard!$Z$2,1,0) in the cell to make it more dynamic, it will not work. I have seen a few mentions of this by setting up Cases, but honestly am not that well versed to make it work. Any advise would be great.


VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
       
    If Intersect(Target, Range("U2")) Is Nothing Then Exit Sub
        If IsNumeric(Target.Value) Then
        If Target.Value = 1 Then
            With ActiveSheet.Shapes("Freeform 29")
                .Fill.Transparency = 0.5
                .Fill.ForeColor.RGB = RGB(20, 55, 90)
                .Line.ForeColor.RGB = RGB(20, 55, 90)
                .Line.Transparency = 0
            End With
        ElseIf Target.Value = 0 Then
            With ActiveSheet.Shapes("Freeform 29")
                .Fill.Transparency = 1
                .Fill.ForeColor.RGB = RGB(20, 55, 90)
                .Line.ForeColor.RGB = RGB(20, 55, 90)
                .Line.Transparency = 1
            End With
        End If
        End If
End Sub
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Hi & welcome to MrExcel.
The change event does run if the cell has a formula in it. You could use a calculation event, but that will run whenever any cell on the sheet re-calculates.
Another option would be to look at V2 rather than U2, but that won't register if Z2 on the dashboard changes.
 
Upvote 0
Hi & welcome to MrExcel.
The change event does run if the cell has a formula in it. You could use a calculation event, but that will run whenever any cell on the sheet re-calculates.
Another option would be to look at V2 rather than U2, but that won't register if Z2 on the dashboard changes.
Thanks for the quick reply! I need it to be dynamic so I need it to look at U2.

Can you please provide a little more detail on the change event! Would I just have to add in a code at the end of the sub to make it calculate the page every time?
 
Upvote 0
You would ned to use
VBA Code:
Private Sub Worksheet_Calculate()
   If Range("U2").Value = 1 Then
       With ActiveSheet.Shapes("Freeform 29")
           .Fill.Transparency = 0.5
           .Fill.ForeColor.RGB = RGB(20, 55, 90)
           .Line.ForeColor.RGB = RGB(20, 55, 90)
           .Line.Transparency = 0
       End With
   ElseIf Range("U2").Value = 0 Then
       With ActiveSheet.Shapes("Freeform 29")
           .Fill.Transparency = 1
           .Fill.ForeColor.RGB = RGB(20, 55, 90)
           .Line.ForeColor.RGB = RGB(20, 55, 90)
           .Line.Transparency = 1
       End With
   End If
End Sub
But as I said, it will run whenever any cell recalculates.
 
Upvote 0
Solution
You would ned to use
VBA Code:
Private Sub Worksheet_Calculate()
   If Range("U2").Value = 1 Then
       With ActiveSheet.Shapes("Freeform 29")
           .Fill.Transparency = 0.5
           .Fill.ForeColor.RGB = RGB(20, 55, 90)
           .Line.ForeColor.RGB = RGB(20, 55, 90)
           .Line.Transparency = 0
       End With
   ElseIf Range("U2").Value = 0 Then
       With ActiveSheet.Shapes("Freeform 29")
           .Fill.Transparency = 1
           .Fill.ForeColor.RGB = RGB(20, 55, 90)
           .Line.ForeColor.RGB = RGB(20, 55, 90)
           .Line.Transparency = 1
       End With
   End If
End Sub
But as I said, it will run whenever any cell recalculates.
Great way to simplify it! Thanks for the help. I am trying to do this for Freeform shapes 29-75 and the corresponding cells U2-u50. Is there an easy way to make this a loop so I dont have to repeat it 49 times!
 
Upvote 0
How about
VBA Code:
Private Sub Worksheet_Calculate()
   Dim Cl As Range
   For Each Cl In Range("U2:U50")
      If Cl.Value = 1 Then
          With ActiveSheet.Shapes("Freeform " & Cl.Row + 27)
              .Fill.Transparency = 0.5
              .Fill.ForeColor.RGB = RGB(20, 55, 90)
              .Line.ForeColor.RGB = RGB(20, 55, 90)
              .Line.Transparency = 0
          End With
      ElseIf Cl.Value = 0 Then
          With ActiveSheet.Shapes("Freeform " & Cl.Row + 27)
              .Fill.Transparency = 1
              .Fill.ForeColor.RGB = RGB(20, 55, 90)
              .Line.ForeColor.RGB = RGB(20, 55, 90)
              .Line.Transparency = 1
          End With
      End If
   Next Cl
End Sub
 
Upvote 0
1609256506327.png
Getting an issue with the below. Again, thank you for your help!
ElseIf Cl.Value = 0 Then
With ActiveSheet.Shapes("Freeform " & Cl.Row + 27)
 
Upvote 0
Make sure that the shapes are consecutively numbered from 29 to 77
 
Upvote 0
I found the issue. Needed the range to be U2:U48 to correspond correctly with the shapes! Thanks, you have been alot of help.
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,148
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top