对单元格应用条件格式的VBA

I'm trying to add conditional formatting to a range that checks cell X1 and if it doesn't match it applies the conditions.

我试着将条件格式添加到一个范围内，这个范围会检查cell X1，如果它不匹配，则应用条件。

If i apply it to one cell it works great. however i need it applied to each cell in a range.

如果我把它应用到一个单元格上，效果会很好。但是我需要它应用到一个范围内的每个单元格。

code:

代码:

    Function FindComment(rng As Range, strSearch As String) As Boolean

On Error GoTo err_h:

strSearch = LCase(strSearch)

If Len(strSearch) = 0 Then
    FindComment = False
    Exit Function
End If

If InStr(1, rng.Comment.Text, strSearch, vbTextCompare) > 0 Or InStr(1, rng.Text, strSearch, vbTextCompare) > 0 Then
    FindComment = False
    Exit Function
End If

FindComment = True
Exit Function

err_h:
    FindComment = True
End Function

And to apply the conditional formatting:

并应用条件格式:

Public Sub AddConditionalFormat(rng As Range)

   rng.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=FINDCOMMENT(" & rng.Address(, , xlA1) & ",$X$1)"
    rng.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

    With Selection.FormatConditions(1).Font
        .ColorIndex = 2
    End With

    With rng.FormatConditions(1).Interior
        .Pattern = xlGray75
        .PatternThemeColor = xlThemeColorDark2
        .PatternTintAndShade = 0
        .ColorIndex = 2
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With

    rng.FormatConditions(1).StopIfTrue = False

End Sub

the range range("B6:GD9") are determined as rng.

范围范围(“B6:GD9”)确定为rng。

currently if the results match it just blanks out all cells including the match.

目前，如果结果匹配，它将删除所有单元格，包括匹配。

anyone have an idea of how to easily fix? i'd prefer something that would not lag out the code by applying to each cell etc.

有人知道怎么简单地修复吗?我更喜欢那些不会因为应用到每个单元而影响代码的东西。

1 个解决方案

#1

The Range.Address property defaults to absolute row and column references. You are looking for something like A1 but you are getting $A$1.

的范围内。地址属性默认为绝对行和列引用。你在找像A1这样的东西但你得到1美元。

rng.FormatConditions.Add Type:=xlExpression, Formula1:= _
    "=FINDCOMMENT(" & rng.Cells(1, 1).Address(RowAbsolute:=False, ColumnAbsolute:=False, ReferenceStyle:=xlA1) & ", $X$1)"
'alternate in shorthand
rng.FormatConditions.Add Type:=xlExpression, Formula1:= _
    "=FINDCOMMENT(" & rng.Cells(1, 1).Address(0, 0, xlA1) & ", $X$1)"

Using .Cells(1, 1) should make that formula reference the upper left cell in rng.

使用。cells(1,1)应该使该公式引用rng中的左上角单元格。

#1