VBA基于列中的单词锁定整个行 [英] VBA to Lock Entire Row Based on a Word in a Column

问题描述

即使有所有论坛帖子，我似乎也无法解决这一问题. 如果在其中一列中出现已锁定"一词，我只需要锁定表(Table1)的整行.

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!!

推荐答案

将If cel.Value = Settled Then更改为If cel.Value = "Settled" Then

如果不包含"，则它将被视为变量.

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

提示:始终使用Option Explicit.它将迫使您声明变量并可以发现此类错误.

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

这篇关于VBA基于列中的单词锁定整个行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！