macro help for a beginner

[Danz]

ive merged the 2 formulas togeather as i liked the offset idea in th
first formula, and have now changed it to operate 2 columns, onl
problem this has caused is the i need to offset to a peticular column
as in i am using the macro on columns D & E, when i click a cell i
each of these the tick appears (great) but i want the cell to shift t
the cell in column G (same row). if i change the offset number to (o
3) it is fine for column D but when i click on column E the cell jump
across to column H, any ideas? cheer

 

[Nick Hodge]

Danz

Try this

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim iOffset As Integer
On Error GoTo err_handler
Application.EnableEvents = False
If Not Application.Intersect(Target, Columns("D:E")) Is Nothing Then
If Target.Column = 4 Then
iOffset = 3
Else
iOffset = 2
End If
If IsEmpty(Target.Value) Then
With Target
..Font.Name = "Wingdings"
..Value = Chr(252)
End With
Target.Offset(0, iOffset).Select
Else
Target.Value = ""
End If
End If
err_handler:
Application.EnableEvents = True
End Sub


--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Nick Hodge]

Sorry..forgot to add second offset if you are removing a tick

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim iOffset As Integer
On Error GoTo err_handler
Application.EnableEvents = False
If Not Application.Intersect(Target, Columns("D:E")) Is Nothing Then
If Target.Column = 4 Then
iOffset = 3
Else
iOffset = 2
End If
If IsEmpty(Target.Value) Then
With Target
..Font.Name = "Wingdings"
..Value = Chr(252)
End With
Target.Offset(0, iOffset).Select
Else
Target.Value = ""
Target.Offset(0, iOffset).Select
End If
End If
err_handler:
Application.EnableEvents = True
End Sub


--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)