VBA to Lock Entire Row Based on a Word in a Column

Question

I can't seem to solve this one, even with all the forum posts. I just need to lock an entire row of a table (Table1) if the word "Locked" appears in one of the columns.

 Sub Accounts_Row_Fixed()
 '
 ' Accounts_Row_Fixed Macro
 '

Dim rng As Range
Set rng = Sheet2.Range("Table1[Accounts Row Fixed]")
ActiveSheet.Unprotect "JP"
rng.Select
ActiveCell.FormulaR1C1 = "=IF([@Claim]=""Settled"",""Locked"","""")"

Dim cel As Range, drng As Range
Set drng = Range("Table1[Accounts Row Fixed]")

For Each cel In drng

    If cel.Value = Settled Then

        cel.EntireRow.Locked = True

    End If

Next cel

Range("C6").Select
ActiveSheet.Protect "JP"

 End Sub

The first part of the code works, getting the word in the column, but its the locking part that's not happening. Any ideas? Making the line change colour would also be a bonus!!

Answer 1

Change If cel.Value = Settled Then to If cel.Value = "Settled" Then

If you do not include the " then it will be treated like a variable.

TIP: Always use Option Explicit. it will force you to declare variables and can fish out such errors.

Answer 2

Are you sure your loop is exiting? I would add this line after you lock the row, Exit For. That way you jump out of the loop as soon as you find the cell you're after.

And here's how to change the backcolor of the entire row, cel.EntireRow.Interior.Color = vbRed.