2 macros in one sheet targeting same cells [Solved/Closed]

chester4207 2 Posts Wednesday November 26, 2014Registration date November 28, 2014 Last seen - Nov 26, 2014 at 05:37 PM - Latest reply: chester4207 2 Posts Wednesday November 26, 2014Registration date November 28, 2014 Last seen
- Nov 28, 2014 at 05:56 PM
This is probably a really 'newbie' question but in all honesty, I'm new to macros and need some assistance.

I have a macro written to automatically add a date/time stamp to cell AB10 if cell AA10 shows "Approved". This macro also automatically deletes the contents of AB10 & AC10 if AA10 is blank.

I now need to also make the entire row lock once cell AA10 shows Approved and once there is a value (from a predetermined drop down list) in cell AC10.

This is my existing macro:


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("AA10:AA10000"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 1).ClearContents
.Offset(0, 2).ClearContents
Else
With .Offset(0, 1)
.NumberFormat = "dd mmm yyyy hh:mm"
.Value = Now
End With
End If
Application.EnableEvents = True
End If
End With
End Sub

Can someone show me how to add the necessary code to lock the row as required above? Everything I've tried to add just disables the macro above.

Sincere thanks for any help that you can provide!
See more 

2 replies

Best answer
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Nov 28, 2014 at 11:09 AM
1
Thank you
Hi
I have not tested it, but it should work

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    With Target
        If .Count > 1 Then Exit Sub
        If Not Intersect(Range("AA10:AA10000"), .Cells) Is Nothing Then
            Application.EnableEvents = False

            If IsEmpty(.Value) Then
                .Offset(0, 1).ClearContents
                .Offset(0, 2).ClearContents

            Else
                With .Offset(0, 1)
                    .NumberFormat = "dd mmm yyyy hh:mm"
                    .Value = Now
                End With

                'since at this point AB cell is already populated, we just want to see if AC column is not empty
                If (Not IsEmpty(.Offset(0, 2))) Then
                    'have some thing to so first unprotect sheet
                    ActiveSheet.Unprotect ("")

                     Rows(.Row).Locked = True
                    ActiveSheet.Protect ("")

                End If
            End If

            Application.EnableEvents = True
        End If
    End With
End Sub

Thank you, rizvisa1 1

Something to say? Add comment

CCM has helped 1872 users this month

chester4207 2 Posts Wednesday November 26, 2014Registration date November 28, 2014 Last seen - Nov 28, 2014 at 05:56 PM
0
Thank you
THANK YOU rizvisa1! I used your code, made a couple of tweaks and it worked like a charm. I sincerely appreciate the help!!!