如果单元格的值等于工作簿任何选项卡中另一个单元格的值，则如何对其设置条件格式

Question

• Excel (Office 2007)
• 带有5个选项卡的工作簿
• 在每个选项卡中都有一个列A，每一行中都有绝对URL
• 在每个选项卡中都有一个B列，它仅从A列中在相邻单元格中写入的绝对URL提取域(例如:如果A1包含以下值：https://stackoverflow.com/questions/，那么B1将包含以下值: stackoverflow.com )

因此，我的问题(实际上是我的帮助)是:如何使工作簿选项卡中的每个列(B)单元格在相应单元格中的值与5个工作簿选项卡中的另一列(B)单元格中的值相等时，获得(例如)红色背景。

实际解释如下：

12月25日TAB-1在A23中，我输入一个绝对URL“X/质询/”x B23创建一个值"X“

12月31日TAB-4在A57中我输入一个绝对URL“X/某某-不同/”B57创建一个值"X“

所以我需要B57自动得到一个..。红色背景，这是给我的一个通知，一个来自B57中指定的域的链接已经处理了，在上面的例子中，在6天前。

非常感激的^_^

Answer 1

首先按alt + F11进入visual编辑器。插入一个新模块并粘贴此代码：

Sub Elio()

Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet, ws4 As Worksheet, ws5 As Worksheet
Dim arrLink() As Variant, xrow As Long, arow As Long, alink                 As Variant, i As Long

Set ws1 = ActiveWorkbook.Worksheets("Sheet1")
Set ws2 = ActiveWorkbook.Worksheets("Sheet2")
Set ws3 = ActiveWorkbook.Worksheets("Sheet3")
Set ws4 = ActiveWorkbook.Worksheets("Sheet4")
Set ws5 = ActiveWorkbook.Worksheets("Sheet5")

i = 0
arow = 0
xrow = 2

Do Until ws1.Cells(xrow, 2).Value = ""
arrLink(arow) = ws1.Cells(xrow, 2).Value
xrow = xrow + 1
arow = arow + 1
Loop

xrow = 2

Do Until ws2.Cells(xrow, 2).Value = ""
arrLink(arow) = ws2.Cells(xrow, 2).Value
xrow = xrow + 1
arow = arow + 1
Loop

xrow = 2

Do Until ws3.Cells(xrow, 2).Value = ""
arrLink(arow) = ws3.Cells(xrow, 2).Value
xrow = xrow + 1
arow = arow + 1
Loop

xrow = 2

Do Until ws4.Cells(xrow, 2).Value = ""
arrLink(arow) = ws4.Cells(xrow, 2).Value
xrow = xrow + 1
arow = arow + 1
Loop

xrow = 2

Do Until ws5.Cells(xrow, 2).Value = ""
arrLink(arow) = ws5.Cells(xrow, 2).Value
xrow = xrow + 1
arow = arow + 1
Loop

xrow = 2

Do Until ws1.Cells(xrow, 2).Value = ""
    For i = LBound(arrLink) To UBound(arrLink)
        If arrLink(i) = ws1.Cells(xrow, 2).Value Then
            ws1.Cells(xrow, 2).Style = "Bad"
        Else:
        End If
    Next i
xrow = xrow + 1
Loop

xrow = 2

Do Until ws2.Cells(xrow, 2).Value = ""
    For i = LBound(arrLink) To UBound(arrLink)
        If arrLink(i) = ws2.Cells(xrow, 2).Value Then
            ws2.Cells(xrow, 2).Style = "Bad"
        Else:
        End If
    Next i
xrow = xrow + 1
Loop

xrow = 2

Do Until ws3.Cells(xrow, 2).Value = ""
    For i = LBound(arrLink) To UBound(arrLink)
        If arrLink(i) = ws3.Cells(xrow, 2).Value Then
            ws3.Cells(xrow, 2).Style = "Bad"
        Else:
        End If
    Next i
xrow = xrow + 1
Loop

xrow = 2

Do Until ws4.Cells(xrow, 2).Value = ""
    For i = LBound(arrLink) To UBound(arrLink)
        If arrLink(i) = ws4.Cells(xrow, 2).Value Then
            ws4.Cells(xrow, 2).Style = "Bad"
        Else:
        End If
    Next i
xrow = xrow + 1
Loop

xrow = 2

Do Until ws5.Cells(xrow, 2).Value = ""
    For i = LBound(arrLink) To UBound(arrLink)
        If arrLink(i) = ws5.Cells(xrow, 2).Value Then
            ws5.Cells(xrow, 2).Style = "Bad"
        Else:
        End If
    Next i
xrow = xrow + 1
Loop

End Sub

这将突出显示每个工作表上B列中的所有单元格，这些单元格的值在任何其他工作表上都有重复的值。

您所要做的就是确保每个工作表的名称与工作簿上的工作表名称相匹配。我没有测试这个是因为你可能会出错。