Как я могу заставить Excel автоматически окрашивать ячейки, которые содержат формулу?
Например, если ячейка B4 содержит =SUM(B1:B3) , ячейка B7 содержит =B4-B7 тогда я хотел бы иметь возможность автоматически окрашивать их по-разному, чтобы Excel выглядел следующим образом:
Если я изменю ячейку B6 на формулу, то я ожидаю, что она тоже автоматически изменит цвет.
Оказывается, вы можете использовать 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
Сохраните его, и теперь условное форматирование будет работать.
Здесь есть две части.
Я предлагаю решение 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 и укажите диапазон ячеек. Код будет проходить через каждую ячейку в диапазоне и, если найдена уже существующая формула, он должен изменить шрифт на синий.
Этот небольшой макрос событий:
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
автоматически закрасит шрифт ячейки синим при вводе формулы.
(Он также закрасит все остальные ячейки формулы одновременно синим)
Поскольку это код рабочего листа, его очень легко установить и использовать автоматически:
Если у вас есть какие-либо проблемы, сначала попробуйте на пробную версию.
Если вы сохраните книгу, макрос будет сохранен вместе с ней. Если вы используете версию Excel более поздней, чем в 2003 году, вы должны сохранить файл как .xlsm, а не .xlsx
Чтобы удалить макрос:
Чтобы узнать больше о макросах в целом, смотрите:
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
Макросы должны быть включены, чтобы это работало!