macro to change cells on change of selection

[scottwilsonx]

I have a spreadsheet with a cell (range: "A10") containing a drop down list of values (in this case sequential months). Based on whatever the value is in cell A10, I want to run a macro which changes the contents of cells R5 and R6 to 01/04/2004 and 31/12/2004 respectively.

I have heard of an onclick vba command that you can use, but I am unsure how to continue. Any help with this macro would be gratefully received.
Thanks

Scott.

[anilsolipuram]

Did you use data->"data validation"->list to populate the list of values?


Explain in detail, whenever you change cell a10 the value of R5 and R6 to be 01/04/2004 and 31/12/2004 respectively.So it doesnot matter what value is selected from cell a10.

[scottwilsonx]

Thanks Anilsolipuram,

Yes, I used Data Validation for the list in the range A10.

To answer your point, yes R5 and R6 are to be 01/04/2004 and 31/12/2004 respectively for reach value entered into A10.

The point is that the user can change the dates in R5 and R6, but I want the dates to revert to 1/4/2004 and 31/12/2004 whenever the selection of A10.

Hope this answers your query.
Many thanks for your help/
Scott.

[anilsolipuram]

I am considering that sheet1 is the sheet where you working on

paste the below code at

tools->macros->visual basic editor, double click "sheet1" below "microsoft excel objects"

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$10" Then
Range("r5").Value = "01/04/2004"
Range("r6").Value = "31/12/2004"
End If
End Sub

[scottwilsonx]

Thanks for the reply, I have copied your formulae into my sheet1 and nothing is happening to cellS R5:R6 when I alter the contents of A10.

Any idea why this isnt working ?

[anilsolipuram]

where exactly did you paste the code , can you explain

[scottwilsonx]

I right-clicked on my sheet1 and selected view-code. The copied your code underneath option-explicit.

When this didn't work, I selected tools, macro, visual basic editor and selected sheet1 from there, it didnt work either.

Thanks
Scott

[anilsolipuram]

paste this code and let me know what happens whenever you change a10 cell

Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox Target.Address
If Target.Address = "$A$10" Then
Range("r5").Value = "01/04/2004"
Range("r6").Value = "31/12/2004"
End If
End Sub

[scottwilsonx]

I got 3 message bozes, $h$3, $r$5 and $r$6.

[anilsolipuram]

did you select A10 cell or H3 cell?, try selecting a10 and changing value , you should get $a$10 , $r$5 and $r$6.

[scottwilsonx]

Thanks for your help, it is working now.
Cheers