VB 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]

One way:

Put this in your worksheet code module:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rCell As Range
Dim rH As Range
Dim sReplace As String

Set rH = Intersect(Target.Cells, Range("H:H"))
If Not rH Is Nothing Then
For Each rCell In rH
Select Case rCell.Text
Case "Q1"
sReplace = "Q1 15/05/07"
Case "Q2"
sReplace = "Q2 15/08/07"
Case "Q3"
sReplace = "Q3 15/11/07"
Case "Q4"
sReplace = "Q4 15/02/08"
End Select
If sReplace <> vbNullString Then
On Error Resume Next
Application.EnableEvents = False
rCell.Value = sReplace
Application.EnableEvents = True
On Error GoTo 0
sReplace = vbNullString
End If
Next rCell
End If
End Sub

 

[Don Guillett]

See my ans in your other post. Pls do NOT post in more than ONE group.