vba code ...

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

MarkHear1 - 13 Mar 2007 17:24 GMT

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

Reply to this Message

JE McGimpsey - 13 Mar 2007 17:50 GMT

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.

> Hi All,
>
[quoted text clipped - 11 lines]
> Many thanks,
> Mark

Reply to this Message

Don Guillett - 13 Mar 2007 18:25 GMT

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

Signature

Don Guillett
SalesAid Software
dguillett1@austin.rr.com

> Hi All,
>
[quoted text clipped - 11 lines]
> Many thanks,
> Mark

Reply to this Message