Vba как выделить близкие по цвету ячейки

Excel — выборка ячеек по цвету заливки

На работе столкнулся с такой задачей — имеется таблица в Excel, в которой ведется табель выходов рабочих в цеху.

В таблице подсчитывается количество часов, фактически отработанных; часов переработки и часов сверх нормы. Так вот, необходимо сделать так, чтобы производилась автоматическая выборка ячеек таблицы по цвету заливки последних.

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

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

В Excel нет встроенных (готовых) инструментов для выборки подобного рода; можно отбирать ячейки только по одному условию — по значению, находящемуся в них. Поэтому решение задачи получалось только одно — через VBA (пользовательские функции).

Прекрасное и готовое решение моей задачи я нашел на сайте http://www.excel-vba.ru/. Даже не одно, а целых два решения, под разные условия. Ниже привожу последовательность шагов, которые привели меня к успеху.

Сразу скажу, что изображения были сделаны в Excel 2007. В Excel 2010 все несколько по другому, но запутаться невозможно, если что.

Режим “Разработчик” в Excel

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

Переходим в “Пуск — Параметры Excel” и находим в левом списке пункт “Надстройки”:

Выбираем в основном окне строчку “Пакет анализа — VBA” и жмем кнопочку “Перейти” в самом низу окна. Откроется еще одно окошко со списком доступных под Excel расширений (надстроек). Снова выбираем в этом списке “Пакет анализа — VBA” и соглашаемся, что хотим установить его, нажав кнопку “ОК”:

Потребуется установочный диск с Microsoft Office на нем (или же подключение к Интернет) чтобы программа получила необходимые пакеты для инсталляции. Если установка прошла успешно, то в “Ленте” появиться пункт “Разработчик” (Excel 2010). Можно перейти в него через эту панель или же с помощью сочетания клавиш Alt + F11 .

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

Вставка готовых функций в Excel VBA

Но есть готовые решения, которые я вставлю в виде кода с помощью меню “Insert — Module”. Просто берем отсюда код функций и вставляем в свой Excel. Затем сохраняем файл Excel с поддержкой VBA (макросов) и все готово для дальнейшей работы.

Вставленные функции появятся в списке формул таблицы:

Ниже представлен готовый код двух функций на VBA, написанных их автором Дмитрием Щербаковым. Первая функция с именем “CountByInteriorColor” выполняет подсчет количества ячеек по цвету заливки.

Вторая функция с именем “SumByInteriorColor” выполняет выборку ячеек по цвету заливки и суммирует все значения в этих ячейках.

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

• — диапазон с ячейками для подсчета
• — ячейка-образец с цветом заливки
• — или учитывает скрытые ячейки; , или опущен(по умолчанию) — скрытые ячейки не подсчитываются.

Функция подсчета количества ячеек

Синтаксис этой функции прост:

Функция подсчета суммы ячеек

Синтаксис этой функции следующий:

При вставке пользовательской функции “CountByInteriorColor” и “SumByInteriorColor” можно воспользоваться либо “Мастером функций”, либо произвести указание диапазона ячеек и ячейку-критерий вручную.

Описание рабочей формулы

Готовый пример работы функции “CountByInteriorColor” можно посмотреть на рисунке “Табель выходов с зелеными ячейками”. В нем подсчет отработанного времени производится по следующей формуле:

Фактически эта формула получается такой (смотри строку №13 на рисунке):

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

Красивая функция trackBy

Пример красивой функции trackBy для Angular. Функция понравилась своей лаконичностью:<% highlight typescript %>public trackByNumber = (_. … Continue reading

Макрос для выборочного выделения ячеек на листе Excel

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

Выделение несмежных диапазонов с помощью макроса

У нас иметься таблица уровней расходов отделов по отношению к расходам труда, произведенного в отдельных отделах за первое полугодие. Часть ячеек, для которых неопределенны показатели содержат значение «нд».

Нам необходимо экспонировать ячейки, которые содержат значение «нд» выделив их серым цветом заливки фона. В таблице на десятки тысяч строк сложно искать все эти ячейки. А если редактировать каждую ячейку вручную, тогда потребуется много сил и времени. Без условно можно воспользоваться инструментом «Условное форматирование». Но иногда для автоматизированного решения данной задачи лучше написать свой собственный макрос. Дело в том, что несмотря на широкие возможности условного форматирования – макрос является более гибким инструментом. Его потом можно применять разным способом. Ведь в основе его принципа действия лежит выделение ячеек с определенным значением. После того как ознакомившись с нашей версией макроса вы сможете его легко изменять, придавая ему все новые функциональные возможности. В результате чего вас будет ограничивать только высота полета вашей фантазии.

Откройте редактор: «РАЗРАБОТЧИК»-«Код»-«Visual Basic» (ALT+F11):

В редакторе создайте новый модуль выбрав инструмент: «Insert»-«Module» и введите в него следующий VBA-код макроса:

Теперь если мы хотим выделить все ячейки которые содержать значение «нд» в таблице отчета уровня расходов по отделам и присвоить им серый фон, тогда выделите диапазон B2:F12 выберите инструмент: «РАЗРАБОТЧИК»-«Код»-«Макросы»-«PoiskZnach»-«Выполнить».

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

После чего макрос выполнит все инструкции, описанные в его коде на языке программирования VBA. Он автоматически и самостоятельно выделит все ячейки в выделенном диапазоне, которые содержат значение «нд»:

Результат автоматического выделения макросом группы ячеек соответствующие критерию поиска.

В начале кода объявлены 4 переменные:

1. i – счетчик цикла.
2. znach – значение которое должна содержать ячейка.
3. diapaz1 – адрес проверяемого диапазона.
4. diapaz2 – адрес для несмежного диапазона, соответствующий критериям пользователя.

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

После декларации переменных вызываем окно для ввода критериев поиска необходимого значения и передаем эти же критерии в переменную znach. Сразу в коде описываем инструкцию для проверки на пустые критерии. Если пользователь оставил поле ввода пустым или нажал на кнопку «Отмена», тогда работа макроса будет прервана остановкой с помощью вызова соответствующей инструкции:

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

На следующей строке кода оператором Set создаем ссылку и в переменную diapaz1 присваиваем диапазон ячеек, который состоит из двух частей:

1. адрес выделенного диапазона ячеек.
2. адрес используемого диапазона рабочего листа Excel.

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

Set diapaz1 = Application.Intersect(Selection, ActiveSheet.UsedRange)

Поэтому если предварительно не был выделен диапазон ячеек в области используемого диапазона рабочего листа Excel, тогда объект для переменной diapaz1 будет пустым (без ссылки). И дальнейшая работа макроса – не возможна. Поэтому следует проверить не является ли пустым объект в переменной diapaz1:

If diapaz1 Is Nothing Then ‘проверяем пустой ли объект Range в переменной diapaz1

Если перед выполнением макроса не был выделен диапазон в используемой области листа – объект пустой, тогда выводиться соответствующее сообщение с помощью функции:

MsgBox «Сначала выделите диапазон!»

И выполнение кода снова прерывается функцией Exit Sub. Если же пользователь сделал все верно, тогда дальше беспрепятственно выполняется следующая строка в коде макроса.

В следующей части VBA-макроса создан цикл, внутри которого по очереди все ячейки в диапазоне определенным переменной diapaz1 проверяются на наличие искомого значения:

For i = 1 To diapaz1.Count
If diapaz1(i) = znach Then
If diapaz2 Is Nothing Then
Set diapaz2 = diapaz1(i)
Else
Set diapaz2 = Application.Union(diapaz2, diapaz1(i))
End If
End If
Next

Если диапазон переменной diapaz1 содержит ячейку с таким же значением, как и в переменной znach, тогда несмежный диапазон ячеек в переменной diapaz2 увеличивается на один адрес для включения этой ячейки. Таким образом несмежный диапазон в переменной diapaz2 включая в себя все адреса найденных ячеек. Реализовывается увеличение несмежного диапазона в переменной diapaz2 с помощью свойства Union, оно позволяет создавать несмежные диапазоны.

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

MsgBox «Не найдено исходное значение»

Макрос для пересчета выделенной области

Теперь вы самостоятельно можете модифицировать макрос. гибко настраивая его под самые привередливые потребности. Например, добавим новую возможность. Макрос будет сообщать нам о количестве найденных ячеек, соответствующих критерию поиска. Для этого в конце макроса после строки diapaz2.Select следует добавить новую строчку кода:

MsgBox «Найдено: » & diapaz2.Count & » ячеек!»

После выполнения макроса и выделения ним ячеек мы будем получать сообщение о их количестве:

Чтобы макрос умел работать с ключами поиска для многозначных значений (например, звездочка «*» – для всех символов), тогда следует изменить соответствующий оператор в коде. Перейдите на строку макроса №15 где указана инструкция для проверки соответствия значений просматриваемых ячеек в диапазоне со значением переменной znach. Затем измените оператор сравнение на оператор Like:

If diapaz1(i) Like znach Then

Теперь после такой модификации кода макроса в критерии поиска можно использовать ключ звездочку «*» (для всех символов). Например, если мы в поле ввода введем значение «н*» макрос будет учитывать все ячейки, значение которых начинается с символа буквы «н».

В Excel можно использовать только 2 ключа многозначного значения запроса в поиске:

1. «*» – символ звездочки (любые другие символы).
2. «?» – вопросительный знак (отдельный символ) например, так:

Если нужно сделать так, чтобы макрос не был чувствителен к регистру вводимых текстовых символов? Если макрос должен одинаково находит значения не зависимо большая или маленькая буква в критерии поиска и выдавать один и тот же результат? Тогда в строке №15 , где проверяется значение ячейки используйте функцию LCase следующим образом:

If LCase(diapaz1(i)) Like LCase(znach) Then

Задача функции LCase замена всех символов в тексте на нижний регистр (маленькие буквы):

Полная новая версия кода модифицированного макроса для выделения ячеек выглядит так:

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

Vba как выделить близкие по цвету ячейки

Макрос для выделения дубликатов разными цветами ⁠ ⁠

Как известно, чтобы выделить дубликаты цветом в Excel можно воспользоваться специальной опцией в «условном форматировании».

Достаточно выделить диапазон, задать цвет заливки, — и все повторяющиеся (или, наоборот, уникальные) значения будут выделены.

Но иногда требуется, чтобы различные повторяющиеся значения были выделены РАЗНЫМИ ЦВЕТАМИ.

В этом случае, без макросов не обойтись. Нажимаем сочетание клавиш Alt+F11 или кнопку Visual Basic на вкладке Разработчик (Developer), вставляем новый пустой модуль через меню Insert — Module и копируем туда код этого макроса:

Sub ВыделитьДубликатыРазнымиЦветами()

On Error Resume Next

‘ массив цветов, используемых для заливки ячеек-дубликатов

Colors = Array(12900829, 15849925, 14408946, 14610923, 15986394, 14281213, 14277081, _

9944516, 14994616, 12040422, 12379352, 15921906, 14336204, 15261367, 14281213)

Dim coll As New Collection, dupes As New Collection, _

cols As New Collection, ra As Range, cell As Range, n&

Err.Clear: Set ra = Intersect(Selection, ActiveSheet.UsedRange)

If Err Then Exit Sub

ra.Interior.ColorIndex = xlColorIndexNone: Application.ScreenUpdating = False

For Each cell In ra.Cells ‘ запонимаем значение дубликатов в коллекции dupes

Err.Clear: If Len(Trim(cell)) Then coll.Add CStr(cell.Value), CStr(cell.Value)

If Err Then dupes.Add CStr(cell.Value), CStr(cell.Value)

Next cell

For i& = 1 To dupes.Count ‘ заполняем коллекцию cols цветами для разных дубликатов

n = n Mod (UBound(Colors) + 1): cols.Add Colors(n), dupes(i): n = n + 1

Next

For Each cell In ra.Cells ‘ окрашиваем ячейки, если для её значения назначен цвет

cell.Interior.color = cols(CStr(cell.Value))

Next cell

Application.ScreenUpdating = True

End Sub

Теперь можно выделить любой диапазон с данными на листе и запустить макрос с помощью сочетания клавиш Alt+F8 или через кнопку Макросы (Macros) на вкладке Разработчик (Developer).

Плюсом этого макроса является то, что он не виснет при выделении всего листа (или столбца) целиком и не вылетает с ошибкой, если найдено более 55 дубликатов. Макрос взят здесь

607 постов 14K подписчиков

Правила сообщества

2. Публиковать посты соответствующие тематике сообщества

3. Проявлять уважение к пользователям

4. Не допускается публикация постов с вопросами, ответы на которые легко найти с помощью любого поискового сайта.

По интересующим вопросам можно обратиться к автору поста схожей тематики, либо к пользователям в комментариях

Важно — сообщество призвано помочь, а не постебаться над постами авторов! Помните, не все обладают 100 процентными знаниями и навыками работы с Office. Хотя вы и можете написать, что вы знали об описываемом приёме раньше, пост неинтересный и т.п. и т.д., просьба воздержаться от подобных комментариев, вместо этого предложите способ лучше, либо дополните его своей полезной информацией и вам будут благодарны пользователи.

Утверждения вроде «пост — отстой», это оскорбление автора и будет наказываться баном.

Круто. Вот VBA все таки понятный язык. Меня тут подрядили для опенофис макросы написать, я уже запарился пока редактор макросов нашел. А в экселе все просто и понятно. Красота.

Спасибо тебе огромное!

Хорошо то как.
Цветов бы побольше, а то повторяются быстро.

а для google exel такого макроса нет?

Раз уж тут интересные фишки в Excel рассказывают новичкам, хочу спросить.

Есть файлы xml, из элементов которых нужно вытащить информацию и составить из них таблицу.

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

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

Импорт XML тоже не подходит, т.к. с ним удобно работать, если файл один.

Вопрос такой от новичка: какие порекомендовали бы ресурсы для изучения макросов на данном направлении, в частности, по преобразованию xml документов в DomDocument и последующего извлечения информации оттуда?

EXCEL — ЭТИ СТРАШНЫЕ МАКРОСЫ – НАЧАЛО⁠ ⁠

Я решил с двух ног ворваться в тему макросов.

Кто-то про них слышал, кто-то даже видел, отдельные сверхразумы их даже использовали. Сегодня будет ознакомительный пост: что это вообще такое и как с этим начать работать. Обратите внимание – этот пост тех, кто не знает, что такое макросы и никогда с ними не работал

Первым делом нужно включить вкладку «Разработчик». По умолчанию в Excel ее спрятали, чтобы не взорвать мозг юзерам. Идем в Параметры -> Настройка ленты -> Основные вкладки -> Разработчик (поставить галочку).

Теперь идем в эту вкладку, нажимаем «Записать макрос» выбираем имя жмакаем «ок». Все, теперь любые действия в Excel надежным образом записываются.

Давайте теперь что-то сделаем. На пример поменяем заливку ячейки А1, в ячейку A2 напишем значение «Мама, я программист», а в ячейке А3 пропишем формулу текущей даты «=Сегодня()»

Останавливаем запись макроса. Нажимаем иконку «Макросы», выбираем наш макрос как мы его обозвали, нажимаем кнопку «изменить».

Появляется окно Microsoft Visual Basic for Applications. Кстати оно также вызывается комбинацией клавиш (Alt + F11) У меня почему-то вызывается только левым Altом, а правым нет, видимо намекая на то что для написания макросов лучше иметь 2 руки (хотя я и одной нажать могу). Появился редактор языка VBA – это язык, который написан специально под офис чтобы на нем писать макросы. В основном окне видим саму эту запись, которую автоматически сделал Excel.

Sub Макрос2()
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range(«A2»).Select
ActiveCell.FormulaR1C1 = «Мама, я программист»
Range(«A3»).Select
ActiveCell.FormulaR1C1 = «=TODAY()»
Range(«A4»).Select
End Sub

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

Теперь давайте разбираться что делает этот макрос

Sub Макрос2()
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
.PatternTintAndShade = 0
End With

(Весь этот кусок от начала говорит нам о том, что с тем элементом что был выделен ранее происходит некоторое дерьмо, в том числе изменение цвета. Вот там, где Color = 255. Все остальное это параметры заливки, которые по итогу не менялись, но макрорекордер решил их тоже записать, на всякий. Это связано с внутренними особенностями работы excel как я понял. Вообще привыкайте к тому что макрорекордер пишет много того что потом вообще можно удалить. Конструкция With – End With позволяет делать несколько действий с одним объектом, на пример выше берется объект Selection.Interior, то есть фон выбранной области и ряду параметров этой заливки назначаются конкретные значения. То есть With нужен для облегчения записи кода, чтобы Selection.Interior не писать вначале каждой строчки.

Range(«A2»).Select –выделяем ячейку «A2»
ActiveCell.FormulaR1C1 = «Мама, я программист» – пишем в ячейку значение
Range(«A3»).Select – выделяем ячейку «А3»
ActiveCell.FormulaR1C1 = «=TODAY()» –пишем в ячейку формулу
Range(«A4»).Select – зачем то выделяем ячейку А4.
End Sub

Теперь при создании нового листа и запуске этого макроса он будет делать все то же самое.

Тут стоит понимать, что половину того что записал макрос можно опустить, так как нам важен результат, а не путь по которому к этому результату пришли, а макрорекордер записывает именно путь. На пример вместо всей конструкции With можно записать

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

или писать формулу как в третей ячейке

С формулами и значениями лично мне не понятно, как excel их интерпретирует, но в макрорекордре он записывает любой ввод в ячейку как ввод формулы. Благо лично у меня при написании макросов не возникает необходимости писать формулы в ячейки. На пример вместо вставки формулы как это было выше можно написать Range(“A3”).Value = Date(), тогда макрос вставит сразу текущую дату в ячейку как значение.

Опытные макроделы пишут макросы сразу без их записи макрорекордером, но это полезный инструмент для самостоятельного изучения при написании макросов: если не знаешь, что как делается в VBА то запускаешь и делаешь, потом смотришь что он там написал.

Теперь давайте напишем какой ни будь полезный макрос, я буду писать строчки и как можно подробнее их комментировать. Комментарии от программы отделяются символом «‘», он ставится вначале комментария. Прошу учесть, что я самоучка и многого не знаю, и просто напишу так ка делаю это сам, возможно есть более изящные решения. Напоминаю, что это просто ознакомительный опус для тех, кто не в зуб ногой.

Sub Colorization()
‘начало нашего макроса и его название
Dim x As Integer
‘объявляем переменную х типа интеджер, это тип для целых чисел от -32 768 до 32 767 (2 байта),
‘она нам нужна для перебора ячеек
For x = 1 To ActiveSheet.UsedRange.Rows.Count
‘перебираем х от 1 до конца использованной части листа, то есть не весь лист, а там где есть данные.
‘Тут цикл For повторяется от этой строки до строки Next x, которая прописана ниже
If Cells(x, 1).Value = «красный» Then Cells(x, 1).Interior.Color = RGB(255, 0, 0)
‘если значение в ячейке равно «красный» то закрашиваем ячейку в красный цвет. Функция If выполняет часть
‘после Then если условие между If и Then верно. Так как у нас необходимое действие занимает одну
‘строку можно писать в таком виде, если же действий несколько применяется конструкция:
‘If … Then
‘…
‘…
‘End If
If Cells(x, 1).Value = «зеленый» Then Cells(x, 1).Interior.Color = RGB(0, 255, 0)
‘как выше только в зеленый цвет
If Cells(x, 1).Value = «синий» Then Cells(x, 1).Interior.Color = RGB(0, 0, 255)
‘в синий цвет
Next x ‘берем следующее значение х, конец цикла For, который мы начали выше
End Sub ‘конец макроса
Как работает этот макрос: берет первый столбец, сначала 1 ячейку, смотрит что в ней написано, и если это равно «красный», «зеленый» или «синий», то красит фон ячейки в этот цвет, если нет по пропускает. Потом берет вторую и т. д. до конца активной части текущего листа.
Для проверки работы макроса нам нужен лист, где в первом столбце будут случайным образом прописаны цвета «красный», «зеленый», «синий». Запускаем макрос – когда он отработает ячейки будут раскрашены:

Некоторые пояснения: если не писать просто Cells то макрос будет делать все в активном листе активного окна. Но макрос может идти и в другие листы, файлы, даже в другие приложения офиса, но об этом не сегодня.

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

Итак, на этом пока все. Надеюсь теперь те, кто никогда не видел макросов получат о них начальное представление. Дальше буду писать про более практичное применение.

Фильтр по цвету в excel

Фильтрация по цвету шрифта, цвету ячеек или наборам значков

​Смотрите также​​ в эти два​ = CStr(arr(i, 1))​’​ залитые цветом ячейки​ была сделана руками,​ курсор мышки на​ выберите значение «Наименование».​Фильтр​ данные из списка​ этой строке не​ данных​ для каждого диапазона​Фильтрация данных в сводной​данные​Применение, создание и удаление​Фильтр​Примечание:​ цвета (в оба​If ar1(i -​’​

​ (заливка произведена большим​​ а не УФ​ опцию «Фильтр по​ В секции «Сортировка»​

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

​ActiveSheet.Range(«$A$1:$A$17»).AutoFilter Field:=1, Operator:=​ количеством цветов -​gtm003​ цвету».​ выберите опцию «Цвет​Будут удалены фильтры всех​ на рисунке:​

​Не устанавливайте флажок, если​​ не используйте смешанные​​ столбцов. Например, можно​​Использование расширенных условий фильтрации​​Фильтр​

​Примечание:​Щелкните стрелку _з0з_ в​ оперативнее обеспечивать вас​

​ не было. В​​ Then Exit For​​ _​​ около 10-15)? Незалитых​​: Спасибо, я так​​Из всплывающего подменю выберите​​ ячейки». В секции​​ столбцов в таблице​​Выбрав параметр​​ вы хотите, чтобы​​ типы данных, такие​

​ выполнить фильтрацию по​Удаление фильтра​.​ Мы стараемся как можно​ столбце с содержимым,​ актуальными справочными материалами​ приложенном примере я​

​Next: Next​​xlFilterNoFill​​ — около половины.​​ и предполагала, что​​ зеленый цвет.​

​ «Порядок» раскройте выпадающее​​ или диапазоне и​Числовые фильтры​ Excel Online добавил​

​ как текст и​​ списку чисел или​​В отфильтрованных данных отображаются​​Щелкните стрелку​​ оперативнее обеспечивать вас​​ которое нужно отфильтровать.​​ на вашем языке.​​ должен видеть при​​.AutoFilter​​End Sub​​Есть ли такая​

См. также

​ из-за него не​В результате отфильтруються данные​ меню «Нет цвета»​

​ отображаются все данные.​

​вы можете применить​ заполнители заголовков (которые​

Фильтрация данных в диапазоне или таблице

​ число, а также​​ критериям, а не​ только те строки,​в заголовке столбца.​ актуальными справочными материалами​В разделе​ Эта страница переведена​ фильтрации столбца В:​.AutoFilter Field:=1, Criteria1:=ar1,​tsap​ возможность?​ работает. Жалко.​ и будут отображаться​ и нажмите на​Создатели Excel решили, начиная​ один из перечисленных​ вы можете переименовать)​ числа и даты​ по обоим; можно​ которые соответствуют указанному​Выберите​

​ на вашем языке.​Фильтр​ автоматически, поэтому ее​ только полную строку​ Operator:=xlFilterValues​:​Гость​_Boroda_​ только те, которые​ кнопку зеленого квадратика.​ от 2007-ой версии​ ниже настраиваемых фильтров.​ над данными.​ в одном столбце,​ отфильтровать по значку​

​ _з0з_ и скрывают​

​текстовые фильтры​ Эта страница переведена​во всплывающем меню​ текст может содержать​ Анна, С: две​

Фильтрация диапазона данных

​: Необходимо вызвать окно​​: Перепишите УФ, как​​ содержать ячейки с​​Нажмите на кнопку «Копировать​​ ввести возможность сортировки​

​В этом примере, чтобы​​Нажмите кнопку​

​ так как для​​ или настраиваемому фильтру,​​ строки, которые не​​или​​ автоматически, поэтому ее​По цвету​​ неточности и грамматические​​ строки — Анна​

​End Sub​, ваш код выполняет​​ настраиваемой сортировки(работает с​​ в приложенном файле​

Фильтрация данных в таблице

​ зеленым цветом заливки:​ уровень» и в​ данных по цвету.​ отобрать регионы, в​

​ОК​​ каждого столбца доступно​ но не к​ должны отображаться. После​

​Числовые фильтры,​​ текст может содержать​​щелкните​ ошибки. Для нас​ и Сергей, и​

​PS Excel >2003​​ фильтрацию в точности​​ 2007 версии) и​

​ и все прекрасно​Обратите внимание! В режиме​ этот раз укажите​​ Для этого послужило​​ которых сумма продаж​.​ только один тип​

Статьи по теме

​ обоим.​ фильтрации данных можно​а затем выберите​

​ неточности и грамматические​Цвет ячейки​ важно, чтобы эта​

​ да наоборот. Надо​

​ задать способ сортировки​

​ заработает​ автофильтра выпадающие меню​ желтый цвет в​ поводом большая потребность​ за март была​Чтобы применить фильтр, щелкните​ команды фильтра. При​Повторное применение фильтра​ копировать, искать, изменять,​ Сравнение, например​ ошибки. Для нас​,​ статья была вам​

​ я хотел бы​: 2012, 2013, 2100,​ оставить видимыми цветные​ — цвет ячейки​gtm003​ так же содержит​ секции «Порядок».​ пользователей предыдущих версий,​ меньше 6000, можно​

​ стрелку в заголовке​​ наличии смешанных типов​Чтобы определить, применен ли​​ форматировать, выносить и​​между​ важно, чтобы эта​Цвет шрифта​ полезна. Просим вас​ ползая по столбцам​ 22222111000​ (залитые цветом) ячейки​tsap​: Спасибо. Получилось!​

Дополнительные сведения о фильтрации

​ опцию «Сортировка по​

​Аналогичным способом устанавливаем новое​ упорядочивать данные в​ применить настраиваемый фильтр:​ столбца и выберите​ данных отображаемая команда​ фильтр, обратите внимание​ выводить на печать​.​ статья была вам​или​ уделить пару секунд​ видеть в отдельном​morozovvv​RAN​:​borus​ цвету»:​ условие для сортировки​

​ такой способ. Раньше​

​Вот как это сделать.​ параметр фильтрации.​ является типом данных,​ на значок в​

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

​ и сообщить, помогла​ месте (VLOOK UP?)​: Уважаемый RAN,​: Вы хочете с​В том-то и​: Здравствуйте!​

​Как всегда, Excel нам​ относительно красного цвета​

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

​и выберите условия.​ ли она вам,​ эти имена (желательно​

​Тема этой страницы​ фильтром? Их есть​ дело — можно​У меня тоже​

​ предоставляет несколько путей​ заливки ячеек. И​ относительно цвета можно​

​ ячейке со словом​ форматировать данные в​

​ используется. Например, если​Стрелка раскрывающегося списка _з0з_​ перемещения.​ОК​ и сообщить, помогла​В диапазоне ячеек или​ с помощью кнопок​ уже без цвета,​ вероятно несложна для​ у меня!​ отфильтровать только по​ не работает фильтр​ для решения одних​ нажмите на кнопку​ было только с​ «Март», выберите пункт​ виде таблицы, вы​ столбец содержит три​ означает, что фильтрация​Кроме того, можно выполнить​.​​ ли она вам,​​ столбце таблицы щелкните​

Фильтрация данных в таблице

​ внизу страницы. Для​ т.е. только текст​ Вас, но актуальна​(Правда без фильтра​ одному цвету, либо​

​ по цвету, из-за​ и тех же​​ ОК.​​ помощью создания макроса​​Числовые фильтры​​ также можете применить​​ значения, хранящиеся в​​ включена, но не​

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

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

​ VBA. Создавалась пользовательская​и условие​ фильтры к диапазону​ виде числа, а​ применяется.​ столбцам. Фильтры являются​

​ таблицу элементы управления​​ внизу страницы. Для​​ цветом, цветом шрифта​

​ ссылку на оригинал​Надеюсь на Вашу​ знаю как решить,​ интересно)​

Фильтрация диапазона данных

​ цветом ячейки. А​ просто нет в​ для себя самый​ на рисунке:​ функция и вводилась​Меньше​

​ данных.​ четыре — в​Если навести указатель мыши​ аддитивными, т. е.​

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

Параметры фильтрации для таблиц или диапазонов

​ непрофессионал в этом​200?’200px’:»+(this.scrollHeight+5)+’px’);»>Sub qqq()​​ как отфильтровать все​​ выпадающем списке, который​ оптимальный путь, плюс​Аналогичным способом можно сортировать​ как формула под​и введите значение​​Выделите данные, которые нужно​​ тексте, откроется команда​ на заголовок столбца​​ каждый дополнительный фильтр​​ в заголовки таблицы.​​ ссылку на оригинал​​ которому вы хотите​Важно:​Читаем Правила форума.​ деле, но Excel​Dim arr, ar,​ незалитые ячейки и​

​ открывается по клику​​ необходимые инструменты всегда​​ данные по цвету​ соответствующим столбцом, по​ 6000.​

​ отфильтровать. Для лучшего​текстовые фильтры​ с включенным фильтром,​ задается на основе​Щелкните стрелку​ (на английском языке).​

​ Часть содержимого этого раздела​ Создаем свою тему​ пользуюсь регулярно. В​​ ar1, cl, i&,​​ вывести все залитые​​ на заголовке фильтруемой​​ под рукой.​ шрифта или типу​

​ которому нужно было​​Нажмите кнопку​​ результата столбцы должны​

​.​ но он не​ текущего фильтра и​в заголовке столбца,​Используйте автофильтр или встроенные​

​На панели инструментов​​ может быть неприменима​​Istonec​​ приведенном коде как​​ j&​

Удаление фильтра из столбца

​ цветом (разными цветами)​​ колонки. Excel 2010.​ ​gtm003​ значка которые содержат​ выполнить сортировку. Теперь​​ОК​​ включать заголовки.​

Удаление всех фильтров из таблицы или диапазона

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

​: Добрый день.​ я понял дублируется​With ActiveSheet.Range(«$A$1:$A$17»)​Гость​

Фильтр и сортировка по цвету ячеек в Excel

​ Как это можно​: Для отслеживания сроков​ ячейки. Для этого​ такие задачи можно​.​На вкладке​ таблицу в заголовки​ подсказка «(отображается все)».​ данных.​ хотите отфильтровать.​ «больше» и «первые​нажмите кнопку​Данные можно быстро фильтровать​Подскажите, как отфильтровать​ функция фильтра по​arr = .Value​: Можно добавить сколько​ исправить?​ возврата сопроводительных писем​ достаточно только указать​ выполнять значительно проще​Excel Online применяет фильтр​

Сортировка по цвету ячеек

​данные​ ее столбцов автоматически​Кнопка фильтра _з0з_ означает,​Примечание:​

​Снимите флажок​ 10″ в _з0з_​Фильтр​ на основании визуальных​ столбец по нескольким​ цвету, но фильтра​

1. ​ReDim ar(UBound(arr) -​ угодно уровней(добавить цвет)​_Boroda_​ на столбе отправки​ соответствующий критерий в​
2. ​ и эффективнее.​ и показывает только​нажмите кнопку​ добавляются элементы управления​ что фильтр применен.​ При использовании диалогового окна​(выделить все)​ , чтобы отобразить​_з0з_.​ условий, таких как​ цветам?​ только по ОДНОМУ​ 1): ReDim ar1(UBound(arr)​
3. ​ -фильтруете-> незалитые ячейки​: — Прочитайте Правила​ стоит условное форматирование.​ секции «Сортировка» диалогового​Пример данных, которые необходимо​
4. ​ регионы, в которых​Фильтр​ фильтрацией.​Если навести указатель мыши​ «​и установите флажки​

​ нужные данные и​Щелкните стрелку​

​ цвет шрифта, цвет​Например есть ячейки​ выбранному цвету. Как​ — 1)​ скрываете​ форума​ И если в​ окна настройки условий.​ отсортировать относительно цвета​

​ сумма продаж меньше​

Фильтр по цвету ячеек

​.​Выделите данные, которые нужно​ на заголовок столбца​Поиск​ для полей, которые​ скрыть остальные. После​в столбце с​ ячейки или наборы​ желтые, есть красные​

1. ​ сделать так, чтобы​.AutoFilter Field:=1, Operator:=xlFilterNoFill​Michael_S​- Создайте свою​
2. ​ этом столбце сделать​​ заливки ячеек изображен​ 6000 долларов.​Можно применить общий фильтр,​ отфильтровать. На вкладке​ с фильтром, в​
3. ​» для поиска​ вы хотите вывести.​

​ фильтрации данных в​ содержимым, которое нужно​ значков. Можно также​ и есть зеленые.​ фильтровался не один​

​For Each cl​: В два этапа​ тему согласно п.5q​ фильтр по цвету,​Аналогично по отношению к​

​ ниже на рисунке:​Аналогичным образом можно применить​ выбрав пункт​Главная​ подсказке отображается фильтр,​ отфильтрованных данных выполняется​Нажмите кнопку​ диапазоне ячеек или​ отфильтровать.​

Не работает фильтр по цвету (Формулы)

​ фильтровать ячейки по​​ Как отфильтровать только​ цвет, а все​ In .Cells.SpecialCells(xlCellTypeVisible)​ с доп. столбцом​ Правил форума​ почему то отображаются​ сортировке, функционирует фильтр​Чтобы расположить строки в​фильтры по дате​Фильтр​нажмите кнопку​ примененный к этому​ поиск только в​ОК​ таблице можно либо​В разделе​​ наличию в них​ красные и желтые?​

​ закрашенные строки по​​ar(i) = cl.Value:​tsap​- Приложите файл​ неправильные значения. Сделала​ по цвету. Чтобы​ последовательности: зеленый, желтый,​

​и​​, или настраиваемый фильтр,​Форматировать как таблицу​ столбцу, например «равно​ отображаемых данных. данные,​

​.​​ повторно применить фильтр​Фильтр​ формата, примененных стилей​vikttur​

​ выбранному столбцу. Например,​​ i = i​

​: Michael S, спасибо​​ с исходными данными​
​ простой файл, в​ разобраться с принципом​ красный, а потом​текстовые фильтры​ зависящий от типа​и выберите команду​ красному цвету ячейки»​ которые не отображаются,​Стрелка в заголовке столбца​ для получения актуальных​во всплывающем меню​

​ или условного форматирования.​​: Не пробовали поляризацинные​ в таблице строки​
​ + 1​ за идею. В​ и желаемым результатом​
​ котором всего одно​ его действия воспользуемся​ без цвета –​.​ данных. Например, при​Форматировать как таблицу​ или «больше 150».​
​ не ищутся. Чтобы​ _з0з_ преобразуется в​

Отфильтровать столбец по цвету в Экселе (Как отфильтровать столбец таблицы по цвету)

​ результатов, либо очистить​​По цвету​
​Более новые версии​
​ очки?​ разной длины и​Next​ принципе, Ваш вариант​ (можно вручную) в​ правило в условном​ тем же диапазоном​ выполним следующий ряд​
​Нажмите кнопку​ фильтрации чисел отображается​

​.​​При повторном применении фильтра​ выполнить поиск по​ значок​ фильтр для повторного​щелкните​

​ Office 2011 ​​Если и это​​ цвета раскраски. При​For i =​ полностью подходит. А​ формате Excel размером​ форматировании и все​ данных, что и​ действий:​Фильтр​ пункт​В диалоговом окне​

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

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

​ 1 To UBound(arr)​​ в один заход​ до 100кб согласно​ равно фильтр по​ в предыдущем примере.​Щелкните на любую ячейку​рядом с заголовком​Числовые фильтры​

​Создание таблицы​​ по следующим соображениям.​ флажок все фильтры.​_з2з_. Щелкните этот значок,​
​Windows Online ​
​,​
​ столбце таблицы щелкните​
​ файл.​
​ увидеть все строки​
​For j =​ интересно, никак не​
​ п.3 Правил форума​
​ цвету работает как-то​

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

​Используйте фильтры, чтобы временно​​Цвет шрифта​ ячейку с определенным​Z​
​ имеющие цвет при​ 0 To UBound(ar)​ получится такое проделать?​
​- Уберите название​
​ странно​Перейдите на любую ячейку​ данных и выберите​
​ команду​
​ пункт​
​ ли в таблице​ или удалены в​С помощью автоФильтра можно​
​ отменить фильтр.​
​ скрывать некоторые данные​или​
​ цветом, цветом шрифта​: Вариант: в доп.​ пересечении с этим​
​ — 1​
​RAN​ сайта из подписи​
​Посмотрите, пожалуйста, в​ диапазона и воспользуйтесь​ инструмент: «ДАННЫЕ»-«Сортировка и​
​Удалить фильтр с​Фильтры по дате​ заголовки.​ диапазоне ячеек или​ создать два типа​
​Обучение работе с Excel:​ в таблице и​
​Значок ячейки​ или значком, по​ поле — код​
​ столбцом. Если у​
​If arr(i, 1)​
​: Спросите у макрорекордера.​tsap​
​ чем дело?​
​ инструментом: «ДАННЫЕ»-«Сортировка и​
​ фильтр»-«Сортировка».​

​.​​, а для текста —​Выберите​

​ столбце таблицы.​​ фильтров: по значению​
​ Фильтрация данных в​ видеть только те,​и выберите цвет.​ которому требуется выполнить​ цвета, иначе фильтр​ меня два цвета​ = ar(j) Then​ Мне сказал что​: Всем доброго дня!​Serge_007​ фильтр»-«Фильтр».​Убедитесь, что отмечена галочкой​Выделите любую ячейку в​Текстовые фильтры​Таблица с заголовками​значения, возвращаемые формулой, изменились,​ списка или по​ таблице​ которые вы хотите.​Использование гистограмм, цветовых шкал​ фильтрацию.​ только, имхо, по​ — розовый и​ ar1(i — 1)​ можно.​Вопрос следующего содержания:​: Фильтр по цвету​Раскройте одно из выпадающих​ опция «Мои данные​ таблице или диапазоне​. Применяя общий фильтр,​, чтобы преобразовать верхнюю​ и лист был​ критериям. Каждый из​Рекомендации и примеры для​Выберите любую ячейку в​ и наборов значков​На вкладке​ одному. ;(​ голубой. Как сделать​ = Empty: Exit​200?’200px’:»+(this.scrollHeight+5)+’px’);»>Sub Макрос4()​Как отфильтровать столбец таблицы​ корректно работает только​ меню, которые появились​ содержат заголовки», а​ и на вкладке​ вы можете выбрать​ строку в заголовки​ пересчитан.​ этих типов фильтров​ сортировки и фильтрации​
​ диапазоне данных.​ для выделения данных​
​Данные​ps В «Копилке»​

Фильтр по нескольким цветам

​ так, чтобы отфильтровались​​ For​
​’​ по цвету. Вернее​ в том случае,​
​ в заголовках столбцов​ после чего из​данные​ для отображения нужные​ таблицы. Данные в​

​Не используйте смешанные типы​​ является взаимно исключающим​ данных по цвету​

​Выберите​Фильтрация списка данных​нажмите кнопку​

​ есть надстройка. -56208-​​ строки, закрашенные только​ar1(i — 1)​’ Макрос4 Макрос​ как отобразить только​ если заливка ячеек​
​ таблицы и наведите​ первого выпадающего списка​

Макрос для выделения дубликатов разными цветами

Как известно, в последних версиях Excel легко выделить дубликаты цветом, — для этого есть специальная опция в «условном форматировании».
Достаточно выделить диапазон, задать цвет заливки, — и все повторяющиеся (или, наоборот, уникальные) значения будут выделены.

Но иногда требуется, чтобы различные повторяющиеся значения были выделены РАЗНЫМИ ЦВЕТАМИ.
В этом случае, без макросов не обойтись.

Ниже приведён макрос, который как раз и решает эту задачу
(достаточно выделить диапазон ячеек, запустить макрос, — и повторяющиеся непустые ячейки получат одинаковый цвет заливки)

• 75949 просмотров

Комментарии

Большое спасибо за макрос, это просто чудо!
Моментально всё выделяет даже на тысячах строк.

Имеется поле суммы столбика В /=СУММ(B1:B23)/, можно сделать так, что эту сумму включались только поля, которые имеют определённый цвет другого поля? Например: при этом столбик А имеет цвет полей от А1 до А10 желтый, а с А11 до А23 красный.Т.Е. что бы автосумм учитывала только поля красного цвета, и при изменении его она пересчитывала результат.
Заранее спасибо!

Евгений, там много в коде переделывать надо. Это только если под заказ (платно)

Отличный инструмент, спасибо!
Подскажите, что нужно изменить в этом макросе, чтоб закрашивались только те ячейки, которые повторяются не меньше 4-х (5-и, . 8-и) раз? Готов каждый раз лазить в макрос и менять на нужное кол-во, только подскажите что и где? (я не спец по макросам, к сожалению).

Добрый день .Есть большой лист Excel. На нем есть отдельные ячейки с цифрами через запятую от 1 до 99. Каждая ячейка содержит 10 цифр в порядке возрастания. Выглядят ячейки так: 44,48,54,59,60,61,64,73,79,97; 23,32,35,38,41,56,62,63,65,84; и т.д. некоторые ячейки из них с повторяющимися цифрами например: 54,59,61,73,78,81,85,87,93,98; 48,54,59,60,64,68,72,77,85,92; 23,35,41,56,60,67,73,83,94,99

4-5 цифр повторяются, остальные разные. Ячейки в которых совпадают все 10 цифр можно автоматически выделить с помощью условного форматирования.
А вот как сделать так, чтобы подобным образом автоматически выделялись цветом ячейки в каторых совпадают 4 цифры и более?
За раннее спасибо!

Александр, тут макрос нужно писать.
Можем сделать под заказ

Добрый день . Есть большой лист Excel. На нем есть отдельные ячейки с цифрами через запятую от 1 до 99. Каждая ячейка содержит 10 цифр в порядке возрастания. Выглядят ячейки так: 44,48,54,59,60,61,64,73,79,97; 23,32,35,38,41,56,62,63,65,84; и т.д. Но некоторые ячейки из них с повторяющимися цифрами например: 54,59,61,73,78,81,85,87,93,98; 48,54,59,60,64,68,72,77,85,92; 23,35,41,56,60,67,73,83,94,99

4-5 цифр повторяются, остальные разные. Ячейки в которых совпадают все 10 цифр можно автоматически выделить с помощью условного форматирования.
А вот как сделать так, чтобы подобным образом автоматически выделялись цветом ячейки в которых совпадают 4 цифры и более?
За раннее спасибо!

а где макрос? не могу скачать

Всё можно
Но это другом макрос нужен. Можем сделать под заказ

Подскажите, а можно ли сделать? Есть 2 столбца (первый составлен из второго с удалением дублей), мне нужно найти все дубли во втором и только то что дублируется окрасить в цвет и в первом и и во втором в один цвет?

Спасибо, супер макрос, очень помог

Де тут лайк ставить !? спасибо . главное что не режет глаза . единственное нужно убрать серый фон или сделать его потемнее . сливается с белым .

Поиск по цвету ячейки в Excel

Cофт

Функция =ЦВЕТЗАЛИВКИ(ЯЧЕЙКА) возвращает код цвета заливки выбранной ячейки. Имеет один обязательный аргумент:

• ЯЧЕЙКА — ссылка на ячейку, для которой необходимо применить функцию.

Ниже представлен пример, демонстрирующий работу функции.

Следует обратить внимание на тот факт, что функция не пересчитывается автоматически. Это связано с тем, что изменение цвета заливки ячейки Excel не приводит к пересчету формул. Для пересчета формулы необходимо пользоваться сочетанием клавиш Ctrl+Alt+F9

Пример использования

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

С помощью функции ЦВЕТЗАЛИВКИ все это становится выполнимым. Например, «протяните» данную формулу с цветом заливки в соседнем столбце и производите вычисления на основе числового кода ячейки.

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

Сортировка по цвету ячеек

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

Чтобы расположить строки в последовательности: зеленый, желтый, красный, а потом без цвета – выполним следующий ряд действий:

• Щелкните на любую ячейку в области диапазона данных и выберите инструмент: «ДАННЫЕ»-«Сортировка и фильтр»-«Сортировка».

• Убедитесь, что отмечена галочкой опция «Мои данные содержат заголовки», а после чего из первого выпадающего списка выберите значение «Наименование». В секции «Сортировка» выберите опцию «Цвет ячейки». В секции «Порядок» раскройте выпадающее меню «Нет цвета» и нажмите на кнопку зеленого квадратика.

• Нажмите на кнопку «Копировать уровень» и в этот раз укажите желтый цвет в секции «Порядок».

• Аналогичным способом устанавливаем новое условие для сортировки относительно красного цвета заливки ячеек. И нажмите на кнопку ОК.

Ожидаемый результат изображен ниже на рисунке:

Аналогичным способом можно сортировать данные по цвету шрифта или типу значка которые содержат ячейки. Для этого достаточно только указать соответствующий критерий в секции «Сортировка» диалогового окна настройки условий.

Фильтр по цвету ячеек

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

• Перейдите на любую ячейку диапазона и воспользуйтесь инструментом: «ДАННЫЕ»-«Сортировка и фильтр»-«Фильтр».

• Раскройте одно из выпадающих меню, которые появились в заголовках столбцов таблицы и наведите курсор мышки на опцию «Фильтр по цвету».

• Из всплывающего подменю выберите зеленый цвет.

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

Обратите внимание! В режиме автофильтра выпадающие меню так же содержит опцию «Сортировка по цвету»:

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

VBA Excel. Цвет ячейки (заливка, фон)

Начиная с Excel 2007 основным способом заливки диапазона или отдельной ячейки цветом (зарисовки, добавления, изменения фона) является использование свойства .Interior.Color объекта Range путем присваивания ему значения цвета в виде десятичного числа от 0 до 16777215 (всего 16777216 цветов).

Заливка ячейки цветом в VBA Excel

Пример кода 1:

Поместите пример кода в свой программный модуль и нажмите кнопку на панели инструментов «Run Sub» или на клавиатуре «F5», курсор должен быть внутри выполняемой программы. На активном листе Excel ячейки и диапазон, выбранные в коде, окрасятся в соответствующие цвета.

Есть один интересный нюанс: если присвоить свойству .Interior.Color отрицательное значение от -16777215 до -1, то цвет будет соответствовать значению, равному сумме максимального значения палитры (16777215) и присвоенного отрицательного значения. Например, заливка всех трех ячеек после выполнения следующего кода будет одинакова:

Проверено в Excel 2016.

Вывод сообщений о числовых значениях цветов

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

Пример кода 2:

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

Использование предопределенных констант

В VBA Excel есть предопределенные константы часто используемых цветов для заливки ячеек:

Предопределенная константа	Наименование цвета
vbBlack	Черный
vbBlue	Голубой
vbCyan	Бирюзовый
vbGreen	Зеленый
vbMagenta	Пурпурный
vbRed	Красный
vbWhite	Белый
vbYellow	Желтый
xlNone	Нет заливки

Присваивается цвет ячейке предопределенной константой в VBA Excel точно так же, как и числовым значением:

Пример кода 3:

Цветовая модель RGB

Цветовая система RGB представляет собой комбинацию различных по интенсивности основных трех цветов: красного, зеленого и синего. Они могут принимать значения от 0 до 255. Если все значения равны 0 — это черный цвет, если все значения равны 255 — это белый цвет.

Выбрать цвет и узнать его значения RGB можно с помощью палитры Excel:

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

Пример кода 4:

Список стандартных цветов с RGB-кодами смотрите в статье: HTML. Коды и названия цветов.

Очистка ячейки (диапазона) от заливки

Для очистки ячейки (диапазона) от заливки используется константа xlNone :

Свойство .Interior.ColorIndex объекта Range

До появления Excel 2007 существовала только ограниченная палитра для заливки ячеек фоном, состоявшая из 56 цветов, которая сохранилась и в настоящее время. Каждому цвету в этой палитре присвоен индекс от 1 до 56. Присвоить цвет ячейке по индексу или вывести сообщение о нем можно с помощью свойства .Interior.ColorIndex:

Пример кода 5:

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

Пример кода 6:

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

Подробнее о стандартной палитре Excel смотрите в статье: Стандартная палитра из 56 цветов, а также о том, как добавить узор в ячейку.

86 комментариев для “VBA Excel. Цвет ячейки (заливка, фон)”

Спасибо, наконец то разобрался во всех перипетиях заливки и цвета шрифта.

Пожалуйста, Виктор. Очень рад, что статья пригодилась.

как проверить наличие фона?

Привет, Надежда!
Фон у ячейки есть всегда, по умолчанию — белый. Отсутствие цветного фона можно определить, проверив, является ли цвет ячейки белым:

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

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

Каким образом можно использовать не в процедуре, а именно в пользовательской функции VBA свойство .Interior.Color?
Скажем, проверять функцией значение какой-то ячейки и подкрашивать ячейку в зависимости от этого.

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

Однако, с помощью пользовательской функции VBA можно вывести значения свойств ячейки, в которой она размещена:

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

Для подкрашивания ячейки в зависимости от ее значения используйте процедуру Sub или штатный инструмент Excel – условное форматирование.

а как можно закрасить только пустые ячейки ?

Лев, закрасить пустые ячейки можно с помощью цикла For Each… Next:

Евгений, спасибо за ссылку на интересный прием.

Евгений, день добрый.
Подскажите пожалуйста, как назначить ячейке цвет через значение RGB, которое в ней записано. Или цвет другой ячейки.

Привет, Александр!
Используйте функцию InStr, чтобы найти положение разделителей, а дальше функции Left и Mid. Смотрите пример с пробелом в качестве разделителя:

Или еще проще с помощью функции Split:

Добрый день!
подскажите, пожалуйста, как можно выводить из таблицы (150 столбцов х 150 строк) адрес ячеек (списком), если они имеют заливку определенного цвета.
Заранее спасибо!

Привет, Валентина!
Используйте два цикла For…Next. Определить числовой код цвета можно с помощью выделения одной из ячеек с нужным цветом.

столбец «D» имеет разноцветную заливку
надо справа от зеленой ячейки написать «Да»

Каким-то образом надо узнать числовое значение цвета, если он не стандартный — vbGreen. Например, можно выделить ячейку с нужным цветом и записать числовое значение цвета в переменную:

Евгений, спасибо за подсказку.
Все получилось

добрый день! подскажите, пожалуйста, как сделать, чтобы результаты выводились на отдельный лист ?
заранее спасибо!

Валентина, замените в коде имя «Лист2» на имя своего листа.

Евгений. Долгое время мучаюсь реализацией следующего сценария: в таблице Excel, которая является базой данных пациентов отделения есть столбец «G» в котором лаборанты отмечают исследования выполненные с контрастом «(С+)» и без «(C-)» и далее в столбце «N» они отмечаются количество использованного контраста «от 50мл до 200мл»; для удобства ввода и уменьшения числа непреднамеренных ошибок в столбцах реализована функция проверки данных что бы сотрудники могли выбирать уже готовые значения из списка и если ошибутся то выскочит ошибка; тем не менее сотрудники умудряются при заполнении таблицы не вносить количество использованного контраста. Вопрос заключается в том, как подкрасить ячейку для ввода количества контраста красным цветом при условии, что в ячейке столбца G фигурирует (С+) с целью акцентировать на этом внимание.
Заранее спасибо за ответ.

Добрый день, Алексей!
Примените условное форматирование:

1 Выберите столбец «N».
2 На вкладке ленты «Главная» перейдите по ссылкам «Условное форматирование» «Создать правило».
3 В открывшемся окне выберите тип правила: «Использовать формулу для определения форматируемых ячеек».
4 В строку формул вставьте =И(ЕСЛИ(G1=»(C+)»;1);ЕСЛИ(N1=»»;1)) . Буква «C» должна быть из одной раскладки (ENG или РУС) в формуле и в ячейке.
5 Нажмите кнопку «Формат» и на вкладке «Заливка» выберите красный цвет.
6 Закройте все окна, нажимая «OK».

Если в ячейке столбца «G» будет выбрано «(С+)», то ячейка той же строки в столбце «N» подкрасится красным цветом. После ввода значения в ячейку столбца «N», ее цвет изменится на первоначальный.

Спасибо Евгений! Ваш пример многое прояснил (в т.ч надо читать Уокенбаха и не филонить). Мне удалось заставить работать этот сценарий не так изящно как у Вас т.е создал для каждой отдельной переменной свое правило: пр. для ГМ. (С+) —> =ЕСЛИ(И(G5066=»ГМ. (С+)»;N5066=»»);»Истина»;»Ложь»)
МТ. (С+) —> =ЕСЛИ(И(G5066=»МТ. (С+)»;N5066=»»);»Истина»;»Ложь») и т.д всего 8 правил для каждого конкретного случая.
И применил их всех для столбца N:N

Ячейку G взял произвольно и в дальнейшем вообще убрал ее на лист метаданных (диапазоны переменных типа ГМ. (С+), МТ. (С+)…)
Еще раз благодарю за помощь! а есть возможность тоже самое сделать цикличным скриптом VBA ? (или я сморозил…).
Заранее спасибо за ответ.