automate a macro when a cell value changes (manually changed by user)

Question

I've a small problem, I know how to automate a Macro but I'm not getting how to do this .

My problem: I have an excel sheet with data from row 8 to end and columns A to AZ. Now in the excel sheet I want to run a macro automatically when the user changes the yellow colored cell it's column is fixed ("N") but row is not fixed as the user can insert rows if they want. how can I do it.

This is the macro I wrote:

Sub Row_Locker()
    Dim locat As String
    Dim colstart As String
    Dim colend As String
    Dim topath As String

    ActiveSheet.Protect Password:="mbt"
    ActiveSheet.Unprotect

    rlocat = ActiveCell.Row
    clocat = ActiveCell.Column
    colstart = "N"

    colend = InputBox("enter the end column name")

    topath = colstart & "8" & ":" & colend & rlocat

    Cells.Select
    ' unlock all the cells
    Selection.Locked = False
    ' next, select the cells (or range) that you want to make read only,
    ' here I used simply A1
    Range(topath).Select
    ' lock those cells
    Selection.Locked = True
    ' now we need to protect the sheet to restrict access to the cells.
    ' I protected only the contents you can add whatever you want
    ActiveSheet.Protect DrawingObjects:=False, Contents:=True, _
    Scenarios:=False, AllowInsertingRows:=True
End Sub

I want the macro to run even if any yellow colored cell value changes. how to do it. the row number is not fixed. the excel sheet looks like this: I'm referring to yellow cell at column N and row 108

enter image description here

You could use a named range. That way when the column/row moves the name stays with it. office.microsoft.com/en-ca/excel-help/…. You can reference that in VBA Range("DataCells") — Matt
Here is my understanding. Please confirm if this is correct. The yellow colored cells are in Col N from row 8 onwards. When that changes, Column O to AZ in that row should be locked or columns A-M and O-P should be locked or A-AZ shold be locked. In the last scenarion, even N is getting locked... Which columns do you want to lock? — Siddharth Rout
After restoring the question, I am not even sure what exact you want. Can you rephrase what exactly are you trying to achieve? — Siddharth Rout

ayaan ayaan · Accepted Answer · 2014-10-31T06:20:35

this is the code I wrote : working perfectly (suggested by Matt)

    Private Sub Worksheet_Change(ByVal Target As Range)
    'If Target.Count > 1 Then Exit Sub
    On Error Resume Next
    Application.EnableEvents = False
        If Not Intersect(Target, Range("D8:D500")) Is Nothing Then Target.Offset(0, 1).MergeArea.ClearContents
        If Target.Row = Range("rowreq").Row Then
        Dim locat As String
        Dim colstart As String
        Dim colend As String
        Dim topath As String
        ActiveSheet.Protect Password:="mbt"
        ActiveSheet.Unprotect
        rlocat = ActiveCell.Row
        clocat = ActiveCell.Column
        colstart = "N"
        colend = InputBox("enter the end column name")
        topath = colstart & "8" & ":" & colend & rlocat
        Cells.Select
        ' unlock all the cells
        Selection.Locked = False
        ' next, select the cells (or range) that you want to make read only,
        ' here I used simply A1
        Range(topath).Select
        ' lock those cells
        Selection.Locked = True
        ' now we need to protect the sheet to restrict access to the cells.
        ' I protected only the contents you can add whatever you want
        ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:=False, AllowInsertingRows:=True
        End If

    Application.EnableEvents = True
    End Sub

automate a macro when a cell value changes (manually changed by user)

1 Answers