☰

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

tag-icon • tag-icon microsoft-excel worksheet-function vba
Форматирование любой ячейки, на которую есть ссылка в формуле

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

Чтобы прояснить мой Queston, у меня есть Raw Data Sheet и Income Sheet. Income Sheet имеет формулы, которые извлекают данные из таблицы в raw data sheet. Что я пытаюсь сделать, так это:Выделите все ячейки в листе необработанных данных, которые упоминаются/на которые ссылаются в листе отчета о прибылях и убытках.; таким образом, я знаю, что что-то в листе необработанных данных не было использовано или не включено в лист доходов. Формула в отчете о прибылях и убытках, по которой я извлекаю данные из листа необработанных данных, выглядит следующим образом:

=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, включает функцию FormulaText(), которая доступна в Excel 2013 и более поздних версиях.

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

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

введите описание изображения здесь

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

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

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

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

Связанный контент