How to make this code work for cells with formulas in them

[daniellerhea]

I currently have a workbook with 3 sheets, in sheet 1 is the matrix and in sheet 3 I have created a kind of search bar using VLOOKUP, I am trying to write a visual basic code so that when a 0 appears from this search bar that the entire column it is in hides itself, the current code I have is

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
Set Target = Intersect(Target, Target.Parent.Rows(4))
If Not Target Is Nothing Then
For Each Cell In Target
Cell.EntireColumn.Hidden = (Cell.Formula = 0)
Next Cell
End If
End Sub

this works when a 0 is entered in row 4 however it does not work when 0 is given from a formula within a cell, can anybody help??

[TMS]

A change event only triggers when a change is made manually, as you have discovered, or through code, not as a result of a change of value of a formula.

You need to monitor the cell(s) the formula depends on and, when one of them changes, check the value in the cell you really want to monitor.


Regards, TMS

[daniellerhea]

What would I need to change in the code to do this? I am quite a novice at VBA

[TMS]

Could the Target you are monitoring have more than one cell?

Which cells do the "Target" depend upon?

Ideally, post a sample workbook.

Regards, TMS