Hi all,

I have a problem on hiding a row when the value of a cell changes.

The following macro which works if i manualy input a zero value into the
cell, but when the cell is a formula and the result is zero, it doesent work
anymore.

Can anybody please help.

Thanks in advance.

the code is as follows:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$7" Then
If Target <= 0 Then
Cells(7, 3).EntireRow.Hidden = True
Else
Cells(7, 3).EntireRow.Hidden = False
End If
End If
End Sub

You need the Calculate Event, not the Change Event.
--
Gary''s Student - gsnu200760


"swiftcode" wrote:

> Hi all,
>
> I have a problem on hiding a row when the value of a cell changes.
>
> The following macro which works if i manualy input a zero value into the
> cell, but when the cell is a formula and the result is zero, it doesent work
> anymore.
>
> Can anybody please help.
>
> Thanks in advance.
>
> the code is as follows:
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Target.Address = "$C$7" Then
> If Target <= 0 Then
> Cells(7, 3).EntireRow.Hidden = True
> Else
> Cells(7, 3).EntireRow.Hidden = False
> End If
> End If
> End Sub

Hi Gary,

Thanks for your help. COuld you show me where to change this event?

"Gary''s Student" wrote:

> You need the Calculate Event, not the Change Event.
> --
> Gary''s Student - gsnu200760
>
>
> "swiftcode" wrote:
>
> > Hi all,
> >
> > I have a problem on hiding a row when the value of a cell changes.
> >
> > The following macro which works if i manualy input a zero value into the
> > cell, but when the cell is a formula and the result is zero, it doesent work
> > anymore.
> >
> > Can anybody please help.
> >
> > Thanks in advance.
> >
> > the code is as follows:
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > If Target.Address = "$C$7" Then
> > If Target <= 0 Then
> > Cells(7, 3).EntireRow.Hidden = True
> > Else
> > Cells(7, 3).EntireRow.Hidden = False
> > End If
> > End If
> > End Sub

Private Sub Worksheet_Calculate()
If Range("C7").Value <= 0 Then
Cells(7, 3).EntireRow.Hidden = True
Else
Cells(7, 3).EntireRow.Hidden = False
End If
End Sub

This replaces your other macro
--
Gary''s Student - gsnu200760


"swiftcode" wrote:

> Hi Gary,
>
> Thanks for your help. COuld you show me where to change this event?
>
> "Gary''s Student" wrote:
>
> > You need the Calculate Event, not the Change Event.
> > --
> > Gary''s Student - gsnu200760
> >
> >
> > "swiftcode" wrote:
> >
> > > Hi all,
> > >
> > > I have a problem on hiding a row when the value of a cell changes.
> > >
> > > The following macro which works if i manualy input a zero value into the
> > > cell, but when the cell is a formula and the result is zero, it doesent work
> > > anymore.
> > >
> > > Can anybody please help.
> > >
> > > Thanks in advance.
> > >
> > > the code is as follows:
> > >
> > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > If Target.Address = "$C$7" Then
> > > If Target <= 0 Then
> > > Cells(7, 3).EntireRow.Hidden = True
> > > Else
> > > Cells(7, 3).EntireRow.Hidden = False
> > > End If
> > > End If
> > > End Sub

Hi Gary,

It works great. Thank you very much for your help.

Rgds
SC

"Gary''s Student" wrote:

> Private Sub Worksheet_Calculate()
> If Range("C7").Value <= 0 Then
> Cells(7, 3).EntireRow.Hidden = True
> Else
> Cells(7, 3).EntireRow.Hidden = False
> End If
> End Sub
>
> This replaces your other macro
> --
> Gary''s Student - gsnu200760
>
>
> "swiftcode" wrote:
>
> > Hi Gary,
> >
> > Thanks for your help. COuld you show me where to change this event?
> >
> > "Gary''s Student" wrote:
> >
> > > You need the Calculate Event, not the Change Event.
> > > --
> > > Gary''s Student - gsnu200760
> > >
> > >
> > > "swiftcode" wrote:
> > >
> > > > Hi all,
> > > >
> > > > I have a problem on hiding a row when the value of a cell changes.
> > > >
> > > > The following macro which works if i manualy input a zero value into the
> > > > cell, but when the cell is a formula and the result is zero, it doesent work
> > > > anymore.
> > > >
> > > > Can anybody please help.
> > > >
> > > > Thanks in advance.
> > > >
> > > > the code is as follows:
> > > >
> > > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > > If Target.Address = "$C$7" Then
> > > > If Target <= 0 Then
> > > > Cells(7, 3).EntireRow.Hidden = True
> > > > Else
> > > > Cells(7, 3).EntireRow.Hidden = False
> > > > End If
> > > > End If
> > > > End Sub