Macro better solution than cell formula with date?

[LLr]

Hello,

I have a worksheet in which a cell formula is not the ideal solution and a
macro would seem the better answer. In the columns with the date I have a
formula which returns the current date plus days. Column A has values which
change daily. I need a macro that ideally would, if it was the current date
in the date columns, place the value of column A in the price cell for that
day and not change that value on subsequent days. There date/price columns
go through plus 20 days. Currently I have the formula =IF(TODAY()=B4,A4,0)
in cell C4, the problem with which it changes the value daily and I have not
come up with a cell formula that will place the value of A4 in C4 without
changing it daily. Thank you in advance for any solutions.

(Hope this formats properly in the post to the newsgroup)

Column A ColumnB ColumnC ColumnD ColumnE ColumnF
ColumnG


Plus 1 Day

Plus 2 Days

Plus 3 Days


Price
Date
Price
Date
Price
Date
Price

$6.46
04/08/05
$0.00
4/11/05
$0.00
4/12/05
$0.00

$0.00
01/02/00
$0.00
1/3/00
$0.00
1/4/00
$0.00

$0.00
01/02/00
$0.00
1/3/00
$0.00
1/4/00
$0.00

$0.00
01/02/00
$0.00
1/3/00
$0.00
1/4/00
$0.00

$0.00
01/02/00
$0.00
1/3/00
$0.00
1/4/00
$0.00

$0.00
01/02/00
$0.00
1/3/00
$0.00
1/4/00
$0.00

$0.00
01/02/00
$0.00
1/3/00
$0.00
1/4/00
$0.00

 

[Bob Phillips]

You could try event code

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Range("B4:B23")) Is Nothing Then
With Target
If .Value = Date + .Row - 4 Then
.Offset(0, 1).Value = .Offset(0, -1).Value
Else
.Offset(0, 1).Value = 0
End If
End With
End If

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)

 

[LLr]

Thanks for the response! I will try that this weekend.