Как я могу заставить Excel автоматически окрашивать ячейки, которые содержат формулу?

Например, если ячейка B4 содержит =SUM(B1:B3) , ячейка B7 содержит =B4-B7 тогда я хотел бы иметь возможность автоматически окрашивать их по-разному, чтобы Excel выглядел следующим образом:

Если я изменю ячейку B6 на формулу, то я ожидаю, что она тоже автоматически изменит цвет.

3 ответа3

3

Оказывается, вы можете использовать ISFORMULA с условным форматированием для этого.

С этого сайта:

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

  • Выберите ячейки A2:C4, с ячейкой A2 в качестве активной ячейки.
  • На вкладке «Главная» ленты Excel щелкните команду «Условное форматирование».
  • Нажмите Новое правило
  • Нажмите Использовать формулу, чтобы определить ячейки для форматирования.
  • Введите и формула ISFORMULA, ссылаясь на активную ячейку - A2:= ISFORMULA(A2)
  • Нажмите кнопку «Формат» и выберите цвет заливки для ячеек с формулами - в данном примере серым.
  • Нажмите OK дважды, чтобы закрыть окна.

К сожалению, ISFORMULA работает только в Excel 2016 и выше.

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

Для этого вам нужно открыть редактор VBA (Alt+F11), создать новый модуль (пункт меню "Вставить", затем "Модуль") и поместить этот код в этот модуль:

Public Function IsFormula(ref As Range)
    IsFormula = ref.HasFormula
End Function

Сохраните его, и теперь условное форматирование будет работать.

1

Здесь есть две части.

  1. У вас уже есть лист с формулой.
  2. Идем вперед все, что вы вводите в качестве формулы на том же листе.

Я предлагаю решение VBA следующим образом.

Нажмите ALT + F11 для доступа к редактору VBA. Вставьте модуль из меню вставки. Перейдите в окно кода и вставьте в него следующий код.

Sub ColorFormula()

Dim inrange As Variant
Dim incell As Range
On Error Resume Next
Set inrange = Application.InputBox(Prompt:="Please Select a Range", Type:=8)
If inrange.Rows.Count = 0 Then
    MsgBox ("No Range Selected!")
    End
End If

For Each incell In inrange
    If incell.HasFormula = True Then
        incell.Font.Color = -4165632
    End If
Next

End Sub

Теперь на левой панели щелкните ThisWorkbook и выберите WorkBook SheetChange Event в окне кода. Заполнитель для подпрограммы с End Sub должен быть доступен для того, чтобы вы могли встроить в нее свой код.

Вставьте в него следующий код

If Target.HasFormula = True Then
    Target.Font.Color = -4165632
End If

В этом примере выбран синий цвет, вы можете изменить его на любой другой доступный цвет.

Выйдите из редактора VBA. Теперь каждый раз, когда вы меняете ячейку на любом из листов этой книги, событие SheetChange будет срабатывать, и если это формула, оно изменится на синий шрифт.

Нажмите ALT + F8 и запустите макрос ColorForlmula и укажите диапазон ячеек. Код будет проходить через каждую ячейку в диапазоне и, если найдена уже существующая формула, он должен изменить шрифт на синий.

0

Этот небольшой макрос событий:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Not Target.HasFormula Then Exit Sub
    Cells.SpecialCells(-4123).Font.ColorIndex = 5
End Sub

автоматически закрасит шрифт ячейки синим при вводе формулы.
(Он также закрасит все остальные ячейки формулы одновременно синим)

Поскольку это код рабочего листа, его очень легко установить и использовать автоматически:

  1. щелкните правой кнопкой мыши имя вкладки в нижней части окна Excel
  2. выберите View Code - откроется окно VBE
  3. вставьте материал и закройте окно VBE

Если у вас есть какие-либо проблемы, сначала попробуйте на пробную версию.

Если вы сохраните книгу, макрос будет сохранен вместе с ней. Если вы используете версию Excel более поздней, чем в 2003 году, вы должны сохранить файл как .xlsm, а не .xlsx

Чтобы удалить макрос:

  1. вызвать окна VBE, как указано выше
  2. очистить код
  3. закройте окно VBE

Чтобы узнать больше о макросах в целом, смотрите:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

а также

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

Чтобы узнать больше о макросах событий (код листа), см .:

http://www.mvps.org/dmcritchie/excel/event.htm

Макросы должны быть включены, чтобы это работало!

Всё ещё ищете ответ? Посмотрите другие вопросы с метками .