VBA code to turn on cell protection for a row, based 'Yes' value in one cell - applied to all rows in table

[alexgeorge]

Excel 2010
Data formatted as a table
One user inputs data into the table for a charge they are making. Our finance person later reconciles the charge by changing the cell in the 'Invoiced' column to 'Yes' and entering an invoice #. I'd like to have VBA code that changes the cells to locked so the regular user can't make edits to the line item after the finance person reconciles it.

• I have the VBA code below to change cells a8:g8 to be 'locked' (and thus uneditable) since the cell in the invoiced column = Yes.
• Cells a9:g9.... should be unlocked (and thus still editable) since the invoiced column <> Yes
• My code below works fine for one row, but wondering how I to edit it so it applies to all rows in the table, especially considering we may dynamicly extend the table to add more rows at the bottom

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("f8").Value <> Yes Then
Range("a8:e8").Locked = True
Else
Range("a8:e8").Locked = False
End If
End Sub

Thank you in advance for the help.

Sincerely, Alex

 

[alexgeorge]

memear_one,

Thank you very much for the fantastic (and fast) response to my questions. I appreciate all that you have done to help me out.

Sincerely,

Alex