Hide/Unhide Sheets Value on other cell through formula

[Rioux]

Hello,

I'm stuck so hopefully somebody can help me with this.
I'm trying to Hide or Unhide some sheets depending on the value of another cell.

I have this:

Private Sub Worksheet_Change(ByVal Target As Range)
If [E3] = "YES" Then
Sheets("Sheet2").Visible = True
Else
Sheets("Sheet2").Visible = False
End If
End Sub

The problem is that it will only work if I manually write "YES" on the cell.
I would like it to work when this value appears on the cell through the following:

=IF(D3>0,"YES","NO")

Is there any way to achieve this?

Any other solution to get the sheet to Hide or Unhide depending on the value of D3 would be ok too.

Thanks!

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "D3" Then
      Sheets("sheet2").Visible = Target.Value > 0
   End If
End Sub

 

[Rioux]

Hello Fluff,

Thanks for your reply.

The issue is the same, it seems like the value still needs to be actually written (in this case the number) but doesn't work if I get it through a formula.
In this case a COUNTIF.

 

[Fluff]

Change events do not work if the cell is changed by a formula.
What is the formula in D3 & are those cells changed manually?

 

[mikerickson]

If all the precedent cells for D3 are on the same sheet, this would work

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim keyCells As Range
    Set keyCells = Range("D3")
    On Error Resume Next
    Set keyCells = Application.Union(keyCells, keyCells.Precedents)
    On Error GoTo 0
    If Not Application.Intersect(keyCells, Target) Is Nothing Then
        Sheets("Sheet2").Visible = IIf(Range("D3").Value = "YES", xlSheetVisible, xlSheetHidden)
    End If
End Sub