Connecting 2 formulas into one - i guess very easy

K

Karisteas

New member
Joined
Feb 13, 2018
Messages
1
Reaction score
0
Points
0
Hi I would like to connect 2 separate things into one worksheet_change (better than double click). I am a little green yet so could You please help me out guys?
I tried "else" and "else if" , didnt work out. Thanks in advance


Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rInt As Range
Dim rCell As Range
Dim tCell As Range


Set rInt = Intersect(Target, Range("D: D"))
If Not rInt Is Nothing Then
For Each rCell In rInt
Set tCell = rCell.Offset(0, 1)
If IsEmpty(tCell) Then
tCell = Now
tCell.NumberFormat = "hh:mm"
End If
Next
End If
End Sub








Code: 
Private Sub Worksheet_BeforeDoubleClick(ByVal _
Target As Range, Cancel As Boolean)
Dim rInt As Range
Dim rCell As Range
Dim tCell As Range


Set rInt = Intersect(Target, Range("B:B"))
If Not rInt Is Nothing Then
For Each rCell In rInt
Set tCell = rCell.Offset(0, 1)
If IsEmpty(tCell) Then
tCell = Now
tCell.NumberFormat = "hh:mm"
End If
Next
End If
End Sub
 
Last edited by a moderator:
Welcome to the forum! As a courtesy, I've added code tags for you this time - the # button is available by choosing Go Advanced when typing your post. Please do this for yourself in future. Thanks!
 
Does this work ?
(Every line is commented so you can easily follow)
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    'limit to single cell
    If Target.Count > 1 Then Exit Sub
    'only if in column B or D
    If Target.Column <> 2 And Target.Column <> 4 Then Exit Sub
    'might want to exclude header row(s)
    If Target.Row < 2 Then Exit Sub
    
    ' OK to proceed if you get to here
    If IsEmpty(Target.Offset(, 1)) Then
        'prevent this sub from calling itself
        Application.EnableEvents = False
        'write info
        Target.Offset(, 1) = Format(Now, "hh:mm")
        're-enable events
        Application.EnableEvents = True
    End If
End Sub
 
You must log in or register to reply here.
Back
Top