在2个工作表的2列之间搜索和匹配字符 [英] Searching and matching characters between 2 columns on 2 worksheets

问题描述

我有2个工作表(工作表1和工作表2).我正在尝试搜索工作表1上A列中的字符，并查看工作表2上A列中是否存在这些字符.请注意，这些字符不是固定的，因此可能会添加，删除或更改这些字符.如果它们存在于工作表2中，那么我会将工作表1中的字符加粗到相应的单元格.

I have 2 worksheets (Worksheet 1 & Worksheet 2). I am trying to searching the characters in column A on Worksheet 1 and see if those characters exist in Column A on Worksheet 2. Please note that these characters are not fixed, so characters may be added, removed, or changed. If they exists in Worksheet 2, then I would bold the characters in Worksheet 1 to the corresponding cell.

工作表1

列A

Jack
James
Peter
Angel

工作表2

列A

Jack ate an apple.
Peter flies a kite.
Henry wakes up for work.
James went to school.

到目前为止，我的方法是使用For循环和if-else语句.我当时正在考虑使用InStr在工作表2的这些句子中查找字符.

My approach to this so far is using For loops and if-else statements. I was thinking about using InStr to find the characters within those sentence in Worksheet 2.

Dim StartNum As Integer
Dim EndNum As Integer
Dim rng1 As Range
Dim rng2 As Range

EndNum =

For StartNum = 1 To EndNum
    If rng1.Value = InStr(rng2.Value) Then _
        rng1.font.Bold = True
End If

推荐答案

在工作表1上选择所有A列，并将A1作为活动单元格.选择主页"►条件格式"►新规则" *，然后在出现新格式规则" *"对话框时，选择使用公式来确定要格式化的单元格，然后在 Format值中提供以下内容:这个公式是正确的:文本框.

Select all of column A on worksheet 1 with A1 as the active cell. Choose Home ► Conditional Formatting ► New Rule* and when the **New Formatting Rule* dialog appears, choose Use a formula to determine which cells to format then supply the following in the Format values where this formula is true: text box.

 =isnumber(match("*"&A1&"*", 'Worksheet 2'!A:A, 0))

单击 Format ，然后从 Font 标签中选择 Bold .单击一次确定一次，接受格式设置，然后再次单击确定以创建新规则.

Click Format and select Bold from the Font tab. Click OK once to accept the formatting and then OK again to create the new rule.

我在工作表1 中添加了一些不匹配的名称，并为这些结果添加了加粗的蓝色字体格式更改.

I added a few non-matching names to Worksheet 1 and topped up the formatting change with a bold blue font for these results.

这篇关于在2个工作表的2列之间搜索和匹配字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！