Hiding cells in another worksheet

[JT]

Dear all

I have the following simple macro in a worksheet object to hide
certain columns whenever a change is made to a worksheet.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range

For Each cell In Range("dDataReq1")
Columns(cell.Column).Hidden = False
If cell.Value = 0 Then
Columns(cell.Column).Hidden = True
End If
Next cell

End Sub

The problem I have is that when the range "dDataReq1" is in another
worksheet, and I want the columns in that worksheet to be hidden, I
get the following error:

method "Range" of object "_worksheet" failed.

It is obvious that my method of referencing the range "dDataReq1" is
ineffective when "dDataReq1" is in a different worksheet, but I can't
seem to find a way around it.

Can anybody help with this?

Thanks

JT

 

[JE McGimpsey]

One way:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rCell As Range

For Each rCell In Me.Parent.Names("dDataReq1").RefersToRange
rCell.EntireColumn.Hidden = (rCell.Value = 0)
Next rCell
End Sub

 

[JT]

Thanks, works a treat.

Thanks for also tidying up the rest of the code.