☰

格式化公式中引用的任何单元格

tag-icon • tag-icon microsoft-excel worksheet-function vba
格式化公式中引用的任何单元格

我有两张工作表，一张中有从第二张工作表中提取数据的公式。如果数据是从第二张工作表中提取的，我需要创建一个宏或某种条件格式，以将单元格突出显示为绿色。第二张工作表是原始数据，第一张工作表是将数据分解到不同的表中。

为了澄清我的问题，我有一份原始数据表和一份收入表。收入表有从原始数据表中的表格中提取数据的公式。我想做的是：突出显示原始数据表中在损益表中调用/引用的所有单元格；这样，我就能知道原始数据表中是否有某些内容未使用或未包含在损益表中。我从原始数据表中提取数据的损益表中的公式是：

=IF(ISERROR(VLOOKUP($A643, 'Raw Data'!$A:$N, 4, FALSE)), "-", VLOOKUP($A643, 'Raw Data'!$A:$N, 4, FALSE))

A 列有一个特定代码，该代码出现在原始数据表的同一列中。希望这些信息对您有所帮助。谢谢！我在下面找到了此代码，但不知道如何调整它以在单独的工作表上进行检查。

Sub Button5_Click()

'PLEASE EDIT THIS FIRST BIT. 

Dim row As Integer
row = 1    ' THE STARTING ROW IN YOUR EXCEL SHEET

Dim numberOfRows As Integer
numberOfRows = 5    'THE TOTAL NUMBER OF ROWS YOUR WORKSHEET HAS

Dim columnWithFormula As String
columnWithFormula = "E"    ' THE COLUMN WHERE THE FORMULAs ARE (I suspect you are using B if your example is accurate))

Dim colourIndex As Integer
colourIndex = 26     ' WHAT COLOUR TO HIGHLIGHT COLUMNS. GOOGLE VBa COLOR INDEX

'AND STOP EDITING :)

For row = 1 To numberOfRows

If range(columnWithFormula & row).Value <> "" Then

   Dim result As String

   result = range(columnWithFormula & row).Formula

   result = Replace(result, "(", " ")
   result = Replace(result, ")", " ")
   result = Replace(result, "-", " ")
   result = Replace(result, "+", " ")
   result = Replace(result, "*", " ")
   result = Replace(result, "/", " ")
   result = Replace(result, "=", " ")
   result = Replace(result, ",", " ")

   Dim cells() As String
   cells = Split(Trim(result), " ")

   For j = 0 To UBound(cells)
    range(cells(j)).Interior.ColorIndex = colourIndex
   Next j

End If

Next row


End Sub

答案1

一种不使用任何 VBA 的条件格式方法涉及 Excel 2013 及更高版本中可用的函数 FormulaText()。

创建一个条件公式，其格式为检查公式中是否存在特定工作表的名称。例如，如果工作表名为“原始数据”，则此公式将突出显示引用该工作表的单元格。

=ISNUMBER(SEARCH("raw data",FORMULATEXT(A2)))

在此处输入图片描述

这也可以应用于检查公式是否引用 Excel 表，使用结构化引用，其中工作表名称不出现在公式中，但表名称出现。

=ISNUMBER(SEARCH("RawDataTable",FORMULATEXT(A2)))

对不同的工作表使用不同的条件公式，这是一种快速突出显示非当前工作表中的数据的非常有效的方法。

注意：理论上，对另一个工作表的任何（非结构化）引用都可以通过!引用中工作表名称后的符号来识别，但有时，在创建跨工作表的公式时，对当前工作表上单元格的引用也可能包括工作表名称，因此应谨慎使用这种方法。

相关内容