Change cell according to date

[Phil]

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

 

[Bob Phillips]

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

--

HTH

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

 

[Phil]

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]

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]

Doesnt seem to work Bob, but thanks for trying.

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)


A

 

[Bob Phillips]

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)