Форматирование любой ячейки, на которую есть ссылка в формуле

Question

У меня есть два листа, один с формулами, извлекающими данные из второго. Мне нужно создать макрос или какой-то условный формат, чтобы выделить зеленую ячейку, если данные были извлечены со второго листа. второй лист - это необработанные данные, а первый лист - это разбивка данных на разные таблицы.

Чтобы уточнить мой Queston, у меня есть лист с необработанными данными и лист с доходами. В листе дохода есть формулы, которые извлекают данные из таблицы в необработанном листе данных. То, что я пытаюсь сделать, это: выделить все ячейки в листе исходных данных, которые вызываются / ссылаются в листе отчета о прибылях и убытках ; таким образом, я знаю, не было ли что-то в исходных данных или не включено в отчет о доходах. Формула в отчете о прибылях и убытках, которую я извлекаю из таблицы необработанных данных:

=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

Answer 1

Подход с условным форматированием без VBA включает функцию FormulaText(), которая доступна в Excel 2013 и более поздних версиях.

Создайте условную формулу с форматом, который проверяет, существует ли в формуле имя конкретного листа. Например, если лист называется "необработанными данными", то в этой формуле будут выделены ячейки, которые ссылаются на этот лист.

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

Это также может быть применено к проверке, ссылается ли формула на таблицу Excel, используя структурированные ссылки, где имя листа не входит в формулу, а имя таблицы - нет.

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

Используя разные условные формулы для разных листов, это может быть очень эффективным способом быстрого выделения данных, которые не берутся из текущего листа.

Примечание. Теоретически любая (неструктурированная) ссылка на другой лист может быть идентифицирована с помощью ! ставить подпись после имени листа в ссылке, но иногда при создании формул, проходящих по листам, ссылка на ячейку на текущем листе может также включать имя листа, поэтому этот подход следует использовать с осторожностью.