☰

設定公式中引用的任何單元格的格式

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)))

對不同工作表使用不同的條件公式，這可以是快速突出顯示並非源自當前工作表的資料的非常有效的方法。

注意：理論上，對另一個工作表的任何（非結構化）引用都可以透過!引用中工作表名稱後面的符號來標識，但有時，在建立跨工作表的公式時，對目前工作表上的單元格的引用也可能包括工作表名稱，因此應謹慎使用該方法。

相關內容