[решено] Excel 2007, способ определить, есть ли в непустой ячейке формула

Компьютерный форум OSzone.net (http://forum.oszone.net/index.php)

- Microsoft Office (Word, Excel, Outlook и т.д.) (http://forum.oszone.net/forumdisplay.php?f=115)

Excel 2007, способ определить, есть ли в непустой ячейке формула

Добрый день.
Проблема следующая. Есть расчет в excel, версия на самом деле не очень принципиальна, для определенности -2007.
Один из столбцов вычисляет значения, но в некоторых случаях значение может вбиваться вручную (формула не учитывает все параметры).
Хотел бы выделить ячейки, вычисляющие значения, либо наоборот, значение в которых вбито, используя стандартные средства без применения VBA. Т.е. ячейки с содержимым "=10*10" и "100" должны иметь разную заливку (или иное выделение), несмотря что у обоих будет одинаковое значение "100".

И вообще понять, есть ли разница между такими ячейками (вроде бы есть, т.к. на VBA cells(,).formula="100" для вбитого значения "100" и cells(,).formula="=100" для вбитого "=100", хотя кто его знает)
На данный момент пользуюсь сочетанием "ctrl+~", но очень неудобно.
Пытался решить вопрос условным форматированием, но оно работает на основании значения ячейки - неважно рассчитывается ли оно или вбито.
Пытался подобрать стандартную функцию (чтобы вбить в соседнюю ячейку), но не получилось.
Честно гуглил, правда времени нет на это, хотя по ощущением - должно быть простое и очевидное решение.

Буду благодарен даже за подсказку в направлении, где стоит поискать решение.

1) F5 - Выделить группу ячеек - Формулы - OK

2) Вкладка "Формулы" - Показать формулы (или используйте сочетание клавиш Ctrl + `)

okshef, спасибо. Пользовался вторым способом, а вот про первый забыл - в моем случае он более удобный, т.к. позволяет выделить цветом нужные ячейки (от "ctrl+`" уже реально в глазах рябит, тяжело вглядываться, большой шанс пропустить ячейку).
И все же это не совсем то, что я хотел - т.к. цель расчетов другая, и отвлекаться на контроль этих ячеек сильно не хочется, поэтому хотелось бы реализовать полностью автоматическое условное ~~выделение~~ форматирование.

Нашел способ создавать макрофункцию "=получить.ячейку(48;Лист1!A1)" и присваивать ей имя в диспетчере имен Источник, это именно то, что я хочу - но работает только на одном листе (в данном случае - "Лист1"), если листов будет 20 - нужно присваивать 20 имен :( Соответственно, когда таких файлов несколько, опять же не смешно.
К сожалению в Макрофункциях я мало что понимаю, да и в именах не особо...
Как вариант буду пользоваться F5, наверное самый приемлемый способ :)

a_axe, ну, добавьте в модуль рабочего листа что-нибудь наподобие:

Код:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim objCell As Range

    

    For Each objCell In Target

        If objCell.HasFormula Then

            objCell.Interior.ColorIndex = 27

        Else

            objCell.Interior.ColorIndex = 24

        End If

    Next

End Sub

Если именно:

Цитата:

Цитата a_axe

Один из столбцов »

а прочее не трогать, то:

Код:

Option Explicit



Private Sub Worksheet_Change(ByVal Target As Range)

    Dim objCell As Range

    Dim objTargetRange As Range

    Dim objIntersectRange As Range

    

    Set objTargetRange = ThisWorkbook.Worksheets.Item("Лист1").Range("D2:D20")

    Set objIntersectRange = Application.Intersect(objTargetRange, Target)

    

    If Not objIntersectRange Is Nothing Then

        For Each objCell In objIntersectRange

            If objCell.HasFormula Then

                objCell.Interior.ColorIndex = 27

            Else

                objCell.Interior.ColorIndex = 24

            End If

        Next

    End If

End Sub

Хех, только хотел пожаловаться, что нет «ThisWorksheet» (по аналогии с «ThisWorkbook»), но вовремя вспомнил ;). Для ссылки на рабочий лист, который содержит вышеприведённый код, вместо:

Код:

Set objTargetRange = ThisWorkbook.Worksheets.Item("Лист1").Range("D2:D20")

можно попробовать:

Код:

Set objTargetRange = Me.Range("D2:D20")

Iska, спасибо за развернутый и проработанный ответ. Попробовал ваш способ, и вся моя решимость не использовать VBA растворилась в воздухе)))
Строка "Set objTargetRange = Me.Range("D2:D20")" работает.
Расчет достаточно сложный, но структурированный (однотипные расчетные листы и несколько сводных расчетов - максимум 3 на файл), поэтому вашу программу легко удалось адаптировать к конкретной таблице.

Практически ничего менять не пришлось, адаптировал следующее:
1. Событием сделал изменение в документе

Код:

Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

2. Проверяемым диапазоном сделал открытый лист (с рабочих листов нет ссылок на другие листы)

Код:

Set objTargetRange = ActiveSheet.Range("e2:e20")

3. Добавил проверку, что открытый лист не является сводным

Код:

If Not left(ActiveSheet.Name,7) = "Сводная" Then

okshef, Iska еще раз спасибо за помощь. Всегда приятно, когда кто-то другой делает за тебя твою работу :grin:, особенно когда времени катастрофически нет.

Цитата:

Цитата a_axe

Событием сделал изменение в документе »

~~Будьте готовы к тому, что на поиск/замену не отработает~~ (помнится, читал, но сам не проверял сие).

Update: хотя нет, это относилось к функциям с «Application.Volatile». Кажется ;).