☰

数式で参照されるセルの書式設定

tag-icon • tag-icon microsoft-excel worksheet-function vba
数式で参照されるセルの書式設定

シートが 2 つあり、1 つには 2 つ目のシートからデータを取得する数式が含まれています。データが 2 つ目のシートから取得された場合にセルを緑色で強調表示するには、マクロまたは条件付き書式を作成する必要があります。シート 2 は生データで、シート 1 はデータをさまざまなテーブルに分割したものです。

私の質問を明確にするために、生データ シートと収益シートがあります。収益シートには、生データ シートのテーブルからデータを取得する数式があります。私がやろうとしていることは次のとおりです。損益計算書シートで呼び出される/参照される生データシートのすべてのセルを強調表示します。; こうすることで、生データ シート内の何かが損益計算書で使用または含まれていないかどうかがわかります。生データ シートからデータを取得する損益計算書の式は次のとおりです。

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

A 列には、同じ列の Raw Data Sheet に表示される特定のコードがあります。この情報がお役に立てば幸いです。ありがとうございます。以下のコードを見つけましたが、別のシートで確認するために調整する方法がわかりません。

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() 関数が使用されます。

特定のシートの名前が数式内に存在するかどうかを確認する形式の条件付き数式を作成します。たとえば、シートの名前が「raw data」の場合、この数式ではそのシートを参照するセルが強調表示されます。

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

ここに画像の説明を入力してください

これは、構造化参照を使用して数式が Excel テーブルを参照しているかどうかを確認する場合にも適用できます。この場合、数式にはシート名が含まれず、テーブル名が含まれます。

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

異なるシートに異なる条件式を使用すると、現在のシートに由来しないデータをすばやく強調表示できる非常に効果的な方法になります。

注: 理論的には、別のシートへの (構造化されていない) 参照は、!参照内のシート名の後の記号によって識別できますが、シートをまたがる数式を作成する場合、現在のシートのセルへの参照にもシート名が含まれることがあるため、この方法は注意して使用する必要があります。

関連情報