vba code ...

[MarkHear1]

Hi All,

I would like to create a Macro so that when a cell in column H on a
spreadsheet is edited the contents is checked and changed if it is a
certain value.


Can anybody offer any help as to how I can do this?


Q1 changed to Q1 15/05/07
Q2 changed to Q2 15/08/07
Q3 changed to Q3 15/11/07
Q4 changed to Q4 15/02/08


Many thanks,
Mark

 

[JE McGimpsey]

See one answer in .programming

Please don't post the same question in multiple groups. It tends to
fragment any answers you get, and potentially wastes the time of those
answering questions that have already been answered.

 

[Don Guillett]

right click sheet tab>view code>insert this>change to suit

Private Sub Worksheet_Change(ByVal target As Range)
If Not Intersect(target, Range("h2:h22")) Is Nothing Then
d1 = DateSerial(2007, 5, 15)' change to start next year
Application.EnableEvents = False
Select Case UCase(target.Value)
Case "Q1": x = 0
Case "Q2": x = 3
Case "Q3": x = 6
Case "Q4": x = 9
Case Else
MsgBox "Wrong date"
End Select
target.Value = DateSerial(Year(d1), Month(d1) + x, 15)
Application.EnableEvents = True
End If
End Sub