突出显示单元格包含名称列表中名称的行 [英] Highlighting rows where a cell contains a name from a list of names

问题描述

如何突出显示包含一个单元格的行，该单元格包含我可以指定的名称列表中的名称?

How would I go about highlighting rows which contain a cell that contains a name from a list of names which I can specify?

我认为最好由宏来完成，但是不确定从哪里开始.

I assume this is best done by a macro, but not sure where to start.

推荐答案

将此代码放在模块中

Option Explicit
Public Sub ApplyConditionalFormattingsFromAList()
    '
    ' this code create multiple conditional formattings on current selected cells
    ' using a list of conditions along with its formattings defined in another worksheet.
    ' to use, just select the range and then run this code
    '

    Dim iRng        As Range
    Dim ApplyToRng  As Range
    Dim wsCondition As Worksheet

    ' determine the worksheet that define the conditions and formattings
    ' to do this, create a blank worksheet and name it "Names",
    ' then in the worksheet,
    ' column A of the worksheet should contain the names to highlight, start at [A1]
    ' column B of the worksheet should be filled with the highlight color to apply, working in pair with column A
    Set wsCondition = Worksheets("Names")

    ' i make the Macro to apply to current selection.
    ' i made it this way so that you can reuse this code on different sheets multiple times
    ' anyway, you can change this to apply to a fixed range, which can then be turned into automatic running code.
    ' e.g. Set ApplyToRng = Columns("B")
    Set ApplyToRng = Selection

    ' clear the conditional formattings of current selection. otherwise the list of conditional formatting will keep growing.
    ApplyToRng.FormatConditions.Delete

    ' add the conditions
    For Each iRng In wsCondition.Range([A1].Address, wsCondition.Cells(Rows.Count, 1).End(xlUp))
        ApplyToRng.FormatConditions.Add Type:=xlTextString, String:=iRng.Value, TextOperator:=XlContainsOperator.xlContains
        ApplyToRng.FormatConditions(ApplyToRng.FormatConditions.Count).SetFirstPriority
        ApplyToRng.FormatConditions(1).Interior.Color = iRng.Offset(0, 1).Interior.Color
        ApplyToRng.FormatConditions(1).StopIfTrue = False
    Next iRng
End Sub

工作表名称"看起来像这样

The worksheet "Names" would look like this

这篇关于突出显示单元格包含名称列表中名称的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！