Thread: Change cell according to date

Hi,

In cell H1 i have a value that changes from time to time. In column A i
have dates. Certain cells in column E have the value of H1 others are blank.
If the date in column A has passed i would like the corresponding cell in
column E to remain the unchanged but future values of column E to alter
when a new value is entered in H1.
Its akin to a loan scenario where future payment alter but past dont.
Hope you understand my meaning.
Thanks

E1: =IF(A1<TODAY(),"",$H$1)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Phil" <mrnw31666@blue.co.uk> wrote in message
news:REODd.5866$GG1.1232@text.news.blueyonder.co.uk...
> Hi,
>
> In cell H1 i have a value that changes from time to time. In column A i
> have dates. Certain cells in column E have the value of H1 others are
blank.
> If the date in column A has passed i would like the corresponding cell in
> column E to remain the unchanged but future values of column E to alter
> when a new value is entered in H1.
> Its akin to a loan scenario where future payment alter but past dont.
> Hope you understand my meaning.
> Thanks
>
>

I see what you mean but that formula , when applied to the necessary cells,
is changing the earlier values in column E , if H1 changes, when i want them
to remain at the value that H1 was on the date of entry.

Think like a mortgage. Last months payment was X ( in E20 say) and X = value
in H1. Next months is Y(in E24 say) so when i change H1 to Y i want the X
to stay as X.
Confusing i know but am finding it hard to explain properly.
"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:uozLWIX9EHA.2552@TK2MSFTNGP09.phx.gbl...
> E1: =IF(A1<TODAY(),"",$H$1)
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Phil" <mrnw31666@blue.co.uk> wrote in message
> news:REODd.5866$GG1.1232@text.news.blueyonder.co.uk...
> > Hi,
> >
> > In cell H1 i have a value that changes from time to time. In column A i
> > have dates. Certain cells in column E have the value of H1 others are
> blank.
> > If the date in column A has passed i would like the corresponding cell
in
> > column E to remain the unchanged but future values of column E to alter
> > when a new value is entered in H1.
> > Its akin to a loan scenario where future payment alter but past dont.
> > Hope you understand my meaning.
> > Thanks
> >
> >
>
>

Phil,

You could try event code, as I don't think you can do it with a formula, as
that formula will take one of the values whenever H1 changes.

Here is m y initial shot

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

On Error GoTo ws_exit:
Application.EnableEvents = False
With Target
If .Address = "$H$1" Then
For Each cell In Range(Range("E1"), Range("E1").End(xlDown))
If cell.Offset(0, -4).Value >= Date Then
cell.Value = .Value2
End If
Next cell
End If
End With

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



--

HTH

RP
(remove nothere from the email address if mailing direct)


"Phil" <mrnw31666@blue.co.uk> wrote in message
news:Q3RDd.5971$GG1.4434@text.news.blueyonder.co.uk...
> I see what you mean but that formula , when applied to the necessary
cells,
> is changing the earlier values in column E , if H1 changes, when i want
them
> to remain at the value that H1 was on the date of entry.
>
> Think like a mortgage. Last months payment was X ( in E20 say) and X =
value
> in H1. Next months is Y(in E24 say) so when i change H1 to Y i want the X
> to stay as X.
> Confusing i know but am finding it hard to explain properly.
> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> news:uozLWIX9EHA.2552@TK2MSFTNGP09.phx.gbl...
> > E1: =IF(A1<TODAY(),"",$H$1)
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Phil" <mrnw31666@blue.co.uk> wrote in message
> > news:REODd.5866$GG1.1232@text.news.blueyonder.co.uk...
> > > Hi,
> > >
> > > In cell H1 i have a value that changes from time to time. In column A
i
> > > have dates. Certain cells in column E have the value of H1 others are
> > blank.
> > > If the date in column A has passed i would like the corresponding cell
> in
> > > column E to remain the unchanged but future values of column E to
alter
> > > when a new value is entered in H1.
> > > Its akin to a loan scenario where future payment alter but past dont.
> > > Hope you understand my meaning.
> > > Thanks
> > >
> > >
> >
> >
>
>

Doesnt seem to work Bob, but thanks for trying.
"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:OTsWunY9EHA.4072@TK2MSFTNGP10.phx.gbl...
> Phil,
>
> You could try event code, as I don't think you can do it with a formula,
as
> that formula will take one of the values whenever H1 changes.
>
> Here is m y initial shot
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim cell As Range
>
> On Error GoTo ws_exit:
> Application.EnableEvents = False
> With Target
> If .Address = "$H$1" Then
> For Each cell In Range(Range("E1"), Range("E1").End(xlDown))
> If cell.Offset(0, -4).Value >= Date Then
> cell.Value = .Value2
> End If
> Next cell
> End If
> End With
>
> ws_exit:
> Application.EnableEvents = True
> End Sub
>
> 'This is worksheet event code, which means that it needs to be
> 'placed in the appropriate worksheet code module, not a standard
> 'code module. To do this, right-click on the sheet tab, select
> 'the View Code option from the menu, and paste the code in.
>
>
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Phil" <mrnw31666@blue.co.uk> wrote in message
> news:Q3RDd.5971$GG1.4434@text.news.blueyonder.co.uk...
> > I see what you mean but that formula , when applied to the necessary
> cells,
> > is changing the earlier values in column E , if H1 changes, when i want
> them
> > to remain at the value that H1 was on the date of entry.
> >
> > Think like a mortgage. Last months payment was X ( in E20 say) and X =
> value
> > in H1. Next months is Y(in E24 say) so when i change H1 to Y i want the
X
> > to stay as X.
> > Confusing i know but am finding it hard to explain properly.
> > "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> > news:uozLWIX9EHA.2552@TK2MSFTNGP09.phx.gbl...
> > > E1: =IF(A1<TODAY(),"",$H$1)
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "Phil" <mrnw31666@blue.co.uk> wrote in message
> > > news:REODd.5866$GG1.1232@text.news.blueyonder.co.uk...
> > > > Hi,
> > > >
> > > > In cell H1 i have a value that changes from time to time. In column
A
> i
> > > > have dates. Certain cells in column E have the value of H1 others
are
> > > blank.
> > > > If the date in column A has passed i would like the corresponding
cell
> > in
> > > > column E to remain the unchanged but future values of column E to
> alter
> > > > when a new value is entered in H1.
> > > > Its akin to a loan scenario where future payment alter but past
dont.
> > > > Hope you understand my meaning.
> > > > Thanks
> > > >
> > > >
> > >
> > >
> >
> >
>
>

Phil,

I tested it and it worked so far as I could understand your requirements.

Give some data details, and a description of what does/doesn't happen.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Phil" <mrnw31666@blue.co.uk> wrote in message
news:N%9Ed.6709$GG1.6167@text.news.blueyonder.co.uk...
> Doesnt seem to work Bob, but thanks for trying.
> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> news:OTsWunY9EHA.4072@TK2MSFTNGP10.phx.gbl...
> > Phil,
> >
> > You could try event code, as I don't think you can do it with a formula,
> as
> > that formula will take one of the values whenever H1 changes.
> >
> > Here is m y initial shot
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > Dim cell As Range
> >
> > On Error GoTo ws_exit:
> > Application.EnableEvents = False
> > With Target
> > If .Address = "$H$1" Then
> > For Each cell In Range(Range("E1"), Range("E1").End(xlDown))
> > If cell.Offset(0, -4).Value >= Date Then
> > cell.Value = .Value2
> > End If
> > Next cell
> > End If
> > End With
> >
> > ws_exit:
> > Application.EnableEvents = True
> > End Sub
> >
> > 'This is worksheet event code, which means that it needs to be
> > 'placed in the appropriate worksheet code module, not a standard
> > 'code module. To do this, right-click on the sheet tab, select
> > 'the View Code option from the menu, and paste the code in.
> >
> >
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Phil" <mrnw31666@blue.co.uk> wrote in message
> > news:Q3RDd.5971$GG1.4434@text.news.blueyonder.co.uk...
> > > I see what you mean but that formula , when applied to the necessary
> > cells,
> > > is changing the earlier values in column E , if H1 changes, when i
want
> > them
> > > to remain at the value that H1 was on the date of entry.
> > >
> > > Think like a mortgage. Last months payment was X ( in E20 say) and X =
> > value
> > > in H1. Next months is Y(in E24 say) so when i change H1 to Y i want
the
> X
> > > to stay as X.
> > > Confusing i know but am finding it hard to explain properly.
> > > "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> > > news:uozLWIX9EHA.2552@TK2MSFTNGP09.phx.gbl...
> > > > E1: =IF(A1<TODAY(),"",$H$1)
> > > >
> > > > --
> > > >
> > > > HTH
> > > >
> > > > RP
> > > > (remove nothere from the email address if mailing direct)
> > > >
> > > >
> > > > "Phil" <mrnw31666@blue.co.uk> wrote in message
> > > > news:REODd.5866$GG1.1232@text.news.blueyonder.co.uk...
> > > > > Hi,
> > > > >
> > > > > In cell H1 i have a value that changes from time to time. In
column
> A
> > i
> > > > > have dates. Certain cells in column E have the value of H1 others
> are
> > > > blank.
> > > > > If the date in column A has passed i would like the corresponding
> cell
> > > in
> > > > > column E to remain the unchanged but future values of column E to
> > alter
> > > > > when a new value is entered in H1.
> > > > > Its akin to a loan scenario where future payment alter but past
> dont.
> > > > > Hope you understand my meaning.
> > > > > Thanks
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>