Excel 2013 -> need help having two macro's in a workbook - already have one running

[newbie87]

Hi everyone,

I need help combining two macros into the same sheet. Currently, I am building a status report and want to build some automation behind it. Here is currently the cells that are affected/need to be referenced:

Column G: QC Status (this is a data validation field - picklist of options. Options on a separate sheet)
Column H: Developer Status (this is a data validation field - picklist of options. Options on a separate sheet)
Column J: Next Action Item Due For (open box)
Column M: Date Completed (Date developer marks Column H "Completed")
Column N: Week Completed - (the week of the year the date falls in (formula based))
Column O: QC Date Completed (Date QC'er marks Column G as "Completed")


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rCell As Range
Dim rChange As Range

On Error GoTo ErrHandler
Set rChange = Intersect(Target, Range("H:H"))
If Not rChange Is Nothing Then
Application.EnableEvents = False
For Each rCell In rChange
If rCell = "Completed" Then
With rCell.Offset(0, 5)
.Value = Date
.NumberFormat = "m/d/yyyy"
End With
Else
rCell.Offset(0, 5).Clear
End If
Next

End If


ExitHandler:
Set rCell = Nothing
Set rChange = Nothing
Application.EnableEvents = True
Exit Sub
ErrHandler:
MsgBox Err.Description
Resume ExitHandler
End Sub

Currently, this marco is putting a date in column M based on Column H being moved to "Completed". I need to be able to run this macro, but also, I want to be able to the date the QC'er mark column G as completed and the date should be in column O.

I'm not as familiar with Macro's so I wasn't sure how to basically combine the same macro, just pulling different data in different places, on the same sheet.

I hope this makes sense! Thanks for your help!

[SamT]

Write three Subs; Worksheet_Change, MarkDeveloper, and MarkQC


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'Determine where the changed cell is.
If not Intersect(Target, Range("H:H") Is Nothing Then MarkDeveloper Target 'Pass the changed cell to the sub
If Not Intersect(Target, Range("G:G")) Is Nothing Then MarkQC Target
End Sub


Private Sub MarkDeveloper(Target As Range)
Application.EnableEvents = False 'Don't let this change be detected
If Target = "Completed" Then
Target.Offset(0, 5) = Format(Date, "m/d/yyyy")
Else
Target.Offset(0, 5).Clear
End If
Application.EnableEvents = True
End Sub


Private Sub MarkQC(Target As Range)
Application.EnableEvents = False
If Target = "Completed" Then Target.Offset(0, 8) = Format(Date, "m/d/yyyy"
Application.EnableEvents = True
End Sub

[newbie87]

that worked! That you so much for building that for me. Macro's are like a new language to me, so this is EXACTLY what I needed. Really appreciate the help SamT!

[SamT]

Do you understand how the code works?