Если цвет ячейки желтый то excel vba

Вычисления по цвету ячеек

Помечать ячейки цветом, используя заливку или цвет шрифта, очень удобно и наглядно. Если вы не дальтоник, конечно Трудности возникают тогда, когда по такой раскрашенной таблице возникает необходимость сделать отчет. И если фильтровать и сортировать по цвету Excel в последних версиях научился, то суммировать по цвету до сих пор не умеет.

Чтобы исправить этот существенный недостаток можно использовать несложные пользовательские макрофункции на Visual Basic, которые позволят нам суммировать/подсчитывать количество/среднее арифметическое ячеек с определенным цветом заливки/шрифта.

На вкладке Разработчик (Developer) нажмите кнопку Visual Basic или сочетание клавиш Alt+F11, чтобы открыть редактор макросов. Если такой вкладки у вас не видно, то включите ее в настройках Файл — Параметры — Настройка ленты (File — Options — Customize Ribbon).

В окне редактора вставьте новый модуль через меню Insert — Module и скопируйте туда текст следующих функций:

Function CountByColor(DataRange As Range, ColorSample As Range) As Long
    Dim cell As Range, n As Long
    
    For Each cell In DataRange
        If cell.Interior.Color = ColorSample.Interior.Color Then n = n + 1
    Next cell
    CountByColor = n
End Function

Function SumByColor(DataRange As Range, ColorSample As Range) As Double
    Dim cell As Range, total As Double
    
    For Each cell In DataRange
        If IsNumeric(cell) And cell.Interior.Color = ColorSample.Interior.Color Then total = total + cell.Value
    Next cell
    SumByColor = total
End Function

Function AverageByColor(DataRange As Range, ColorSample As Range) As Double
    Dim cell As Range, total As Double, n As Long
    
    For Each cell In DataRange
        If IsNumeric(cell) And cell.Interior.Color = ColorSample.Interior.Color Then
            total = total + cell.Value
            n = n + 1
        End If
    Next cell
    AverageByColor = total / n
End Function
 

Как легко сообразить, первая функция здесь вычисляет количество ячеек с заданным цветом заливки, вторая — сумму, а третья — среднее арифметическое. У всех функций два аргумента:

• DataRange — диапазон исходных ячеек с числами, раскрашенных в разные цвета
• ColorSample — ячейка, цвет заливки которой мы берём за образец

Если теперь вернуться в Excel, то в Мастере функций (вкладка Формулы — кнопка Вставить функцию) в появившейся там категории Определенные пользователем (User Defined) можно найти наши функции и вставить их на лист. Либо напрямую ввести их в строку формул, как любые другие функции Excel:

Добавление условий

Аналогичный подход можно легко масштабировать, добавляя, при необходимости, дополнительные условия в проверку (команда if … then…). Так, например, если нам нужно при вычислении среднего арифметического учитывать не только цвет заливки, но и цвет шрифта (т.е. считать не просто жёлтые, а именно жёлто-красные ячейки), то код нашей макро-функции будет выглядеть так:

 Function AverageByColor2(DataRange As Range, ColorSample As Range) As Double
    Dim cell As Range, total As Double, n As Long
    
    For Each cell In DataRange
        If IsNumeric(cell) And cell.Interior.Color = ColorSample.Interior.Color And cell.Font.Color = ColorSample.Font.Color Then
            total = total + cell.Value
            n = n + 1
        End If
    Next cell
    AverageByColor2 = total / n
End Function

Разница только в добавленном через логическую связку «И» (and) условии на проверку соответствия цвета шрифта очередной проверяемой ячейки cell.Font.Color цвету шрифта ячейки-образца ColorSample.Font.Color.

Ограничения и нюансы пересчёта

У созданных нами макрофункций есть 2 важных нюанса.

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

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

Полностью решить эту проблему невозможно, но есть несколько способов её обойти:

• Сделать двойной щелчок левой кнопкой мыши по ячейки с нашей формулой и нажать на Enter, т.е. имитировать повторный ввод функции в ячейку — Excel её заново пересчитает и выдаст обновленный результат.
• Можно использовать сочетание клавиш Ctrl+Alt+F9, которое принудительно заставит Excel пересчитать всё формулы и функции независимо от того, изменились ли для них исходные данные. Но это сочетание нужно будет не забывать нажимать каждый раз при изменении исходных данных.
• Добавить в код наших макрофункций (в любое место) команду Application.Volatile True. Эта команда языка Visual Basic заставляет Excel пересчитывать результаты нашей функции при изменении любой ячейки на листе (или по нажатию  F9). Однако, в этом случае, скорость работы наших функций заметно снизится, т.к. их пересчёт будет происходить постоянно — даже когда мы не меняли цвета или значения ячеек в исходных данных. Пользуйтесь этим методом осторожно.

И помните о том, что наша функция перебирает все (и пустые тоже) ячейки в диапазоне DataRange и не задавайте в качестве первого аргумента целый столбец — «думать» будет долго

Ссылки по теме

• Сортировка строк по цвету
• Функции подсчета количества и суммы ячеек по цвету из надстройки PLEX
• Что такое «ад условного форматирования» и как его победить

Авиасимулятор, что же может быть лучше, чем по управлять огромным лайнером, и провести его из одного аэропорта в другой. Авиасимуляторов гражданской авиации довольно немного, среди них Microsoft Flight Simulator X (MFSX), X-Plane и другие, среди которых красуется  FlightGear Flight Simulator . Что его отличает от других ему подобных, это его доступность, симулятор является абсолютно бесплатным. Конечно в нем нет красивых ландшафтов и объектов как в Microsoft Flight Simulator X и X-Plane, но модели самолетов так же достойны внимания. FlightGear Немного расскажу о том, какие нам понадобятся знания для того, что бы совершить полет в FlightGear. Для начала  скачиваем  и устанавливаем последнюю версию игры.  Запускаем. Перед нами меню игры. На вкладке Enviroment  выбираем желаемое время полета. Там же можно выбрать реальное время, но учтите что если вы будите играть вечером, то все полеты будут ночные, по этому для начала ставим лето (summer) и утро или день (morning). На в

Условное форматирование Excel позволяет определять правила, определяющие форматирование ячеек.

Например, вы можете создать правило, выделяющее ячейки, соответствующие определенным критериям. Примеры включают:

• Числа, попадающие в определенный диапазон (например, меньше 0).
• 10 первых пунктов списка.
• Создание «тепловой карты».
• «Формульные» правила практически для любого условного форматирования.

В Excel условное форматирование можно найти на ленте в разделе Главная> Стили (ALT> H> L).

Чтобы создать собственное правило, нажмите «Новое правило», и откроется новое окно:

Условное форматирование в VBA

Доступ ко всем этим функциям условного форматирования можно получить с помощью VBA.

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

Правила условного форматирования также сохраняются при сохранении рабочего листа.

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

Практическое использование условного форматирования в VBA

У вас может быть большой кусок необработанных данных, импортированных на ваш рабочий лист из файла CSV (значения, разделенные запятыми), или из таблицы или запроса базы данных. Это может перетекать в информационную панель или отчет с изменением чисел, импортированных из одного периода в другой.

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

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

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

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

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

Простой пример создания условного формата для диапазона

В этом примере настраивается условное форматирование для диапазона ячеек (A1: A10) на листе. Если число в диапазоне от 100 до 150, то цвет фона ячейки будет красным, в противном случае он не будет иметь цвета.

Обратите внимание, что сначала мы определяем диапазон MyRange применить условное форматирование.

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

Цвета представлены числовыми значениями. Для этого рекомендуется использовать обозначение RGB (красный, зеленый, синий). Для этого можно использовать стандартные цветовые константы, например. vbRed, vbBlue, но вы можете выбрать один из восьми цветов.

Доступно более 16,7 млн ​​цветов, и с помощью RGB можно получить доступ ко всем. Это намного проще, чем пытаться вспомнить, какое число соответствует какому цвету. Каждый из трех номеров цветов RGB составляет от 0 до 255.

Обратите внимание, что параметр «xlBetween» является включительным, поэтому значения ячеек 100 или 150 будут удовлетворять условию.

Многовариантное форматирование

Вы можете настроить несколько условных правил в пределах своего диапазона данных, чтобы все значения в диапазоне охватывались разными условиями:

В этом примере устанавливается первое правило, как и раньше, с красным цветом ячейки, если значение ячейки находится в диапазоне от 100 до 150.

Затем добавляются еще два правила. Если значение ячейки меньше 100, то цвет ячейки синий, а если больше 150, то цвет ячейки желтый.

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

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

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

При этом используется тип xlExpression, а затем используется стандартная формула Excel, чтобы определить, является ли ячейка пустой, а не числовым значением.

Объект FormatConditions является частью объекта Range. Он действует так же, как коллекция с индексом, начинающимся с 1. Вы можете перебирать этот объект, используя цикл For… Next или For… Each.

Удаление правила

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

Этот код создает новое правило для диапазона A1: A10, а затем удаляет его. Вы должны использовать правильный номер индекса для удаления, поэтому проверьте «Управление правилами» в интерфейсе Excel (это покажет правила в порядке выполнения), чтобы убедиться, что вы получили правильный номер индекса. Обратите внимание, что в Excel нет возможности отмены, если вы удаляете правило условного форматирования в VBA, в отличие от того, если вы делаете это через интерфейс Excel.

Изменение правила

Поскольку правила представляют собой набор объектов на основе указанного диапазона, вы можете легко вносить изменения в определенные правила с помощью VBA. Фактические свойства после добавления правила доступны только для чтения, но вы можете использовать метод Modify для их изменения. Доступны для чтения / записи такие свойства, как цвета.

Этот код создает объект диапазона (A1: A10) и добавляет правило для чисел от 100 до 150. Если условие истинно, цвет ячейки меняется на красный.

Затем код изменяет правило на числа меньше 10. Если условие истинно, цвет ячейки теперь меняется на зеленый.

Использование градуированной цветовой схемы

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

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

Когда этот код запускается, он будет градуировать цвета ячеек в соответствии с возрастающими значениями в диапазоне A1: A10.

Это очень впечатляющий способ отображения данных, который наверняка привлечет внимание пользователей.

Условное форматирование значений ошибок

Когда у вас огромный объем данных, вы можете легко пропустить значение ошибки в различных таблицах. Если это будет представлено пользователю без разрешения, это может привести к большим проблемам, и пользователь потеряет уверенность в цифрах. Это использует тип правила xlExpression и функцию Excel IsError для оценки ячейки.

Вы можете создать код, чтобы все ячейки с ошибками имели красный цвет:

Условное форматирование дат в прошлом

У вас могут быть импортированные данные там, где вы хотите выделить даты, которые были в прошлом. Примером этого может быть отчет по дебиторам, в котором вы хотите выделить любые старые даты в счетах-фактурах старше 30 дней.

Этот код использует тип правила xlExpression и функцию Excel для оценки дат.

Этот код принимает диапазон дат в диапазоне A1: A10 и устанавливает красный цвет ячейки для любой даты, прошедшей более 30 дней.

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

Использование панелей данных в условном форматировании VBA

Вы можете использовать VBA для добавления гистограмм к диапазону чисел. Это почти как мини-диаграммы, и они дают мгновенное представление о том, насколько велики числа по отношению друг к другу. Принимая значения по умолчанию для гистограмм, код очень легко писать.

Ваши данные на листе будут выглядеть так:

Использование значков в условном форматировании VBA

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

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

Использование условного форматирования для выделения пятерки лучших

Вы можете использовать код VBA, чтобы выделить 5 первых чисел в диапазоне данных. Вы используете параметр под названием «AddTop10», но вы можете настроить номер ранга в коде на 5. Пользователь может захотеть увидеть самые высокие числа в диапазоне без предварительной сортировки данных.

Данные на вашем листе после запуска кода будут выглядеть так:

Обратите внимание, что значение 145 появляется дважды, поэтому выделяются шесть ячеек.

Значение параметров StopIfTrue и SetFirstPriority

StopIfTrue имеет значение, если диапазон ячеек имеет несколько правил условного форматирования. Одиночная ячейка в пределах диапазона может удовлетворять первому правилу, но также может удовлетворять последующим правилам. Как разработчик, вы можете захотеть, чтобы форматирование отображалось только для первого правила. Другие критерии правила могут перекрываться и вносить непреднамеренные изменения, если им разрешено продолжить работу по списку правил.

По умолчанию для этого параметра установлено значение True, но вы можете изменить его, если хотите, чтобы учитывались все остальные правила для этой ячейки:

Параметр SetFirstPriority указывает, будет ли это правило условия оцениваться первым, если для этой ячейки существует несколько правил.

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

Вы можете изменить приоритет правила:

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

Использование условного форматирования для ссылки на другие значения ячеек

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

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

Следующий код выполнит поиск по вашему списку чисел, отыщет в соседней ячейке форматирование текста, а затем отформатирует число, как требуется:

После того, как этот код был запущен, ваш рабочий лист теперь будет выглядеть так:

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

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

Как вы видели в предыдущих примерах, операторы используются для определения того, как будут оцениваться значения условий, например xlBetween.

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