Vba excel убрать форматирование

Обновлено: 07.07.2024

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

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

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

Range ( "C12:D17" ) . Cells ( 4 ) . Interior . Color = 568569

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

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

Cells ( 2 , 1 ) . Interior . Color = 16777215 + ( - 12207890 )

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

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

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

В 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:

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

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

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

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

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

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

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

Готовую стандартную палитру из 56 цветов можете посмотреть здесь.

Содержание рубрики VBA Excel по тематическим разделам со ссылками на все статьи.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Range ( "A1" ) . Interior . Color = RGB ( Left ( Range ( "A1" ) , n1 - 1 ) , _ Mid ( Range ( "A1" ) , n1 + 1 , n2 - n1 - 1 ) , Mid ( Range ( "A1" ) , n2 + 1 ) )

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

Range ( "A1" ) . Interior . Color = RGB ( a ( 0 ) , a ( 1 ) , a ( 2 ) )

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

spisok = spisok & vbNewLine & Cells ( i1 , i2 ) . Address If Cells ( i , "D" ) . Interior . Color = c Then Cells ( i , "E" ) = "Да"

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

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

Sheets ( "Лист2" ) . Cells ( n , 1 ) = "Список адресов ячеек с цветом " & tsvet Sheets ( "Лист2" ) . Cells ( n , 1 ) = Cells ( i1 , i2 ) . Address

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

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

Легко форматируйте ячейки на основе критериев Kutools for Excel

Удалите условное форматирование, но сохраните формат с помощью VBA

В Excel нет прямого метода удаления условного форматирования, кроме VBA.

1. Включите лист, на котором вы хотите удалить условные правила форматирования, но сохранить формат, и нажмите Alt + F11 ключи для открытия Microsoft Visual Basic для приложений окно.

Нажмите 2 Вставить > Модульи вставьте ниже VBA в скрипт модуля. Смотрите скриншот:

VBA: удалите правила условного форматирования, но сохраните формат.

3. Нажмите F5 нажмите клавишу для запуска кода, и появится диалоговое окно для выбора диапазона для удаления условного форматирования. Смотрите скриншот:

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

Легко форматируйте ячейки на основе критериев Kutools for Excel

Вот удобный инструмент - Kutools for Excel, вы можете быстро и легко форматировать ячейки на основе критериев без формул с помощью Выбрать определенные ячейки утилита.

После бесплатная установка Kutools for Excel, сделайте следующее:

1. Выберите диапазон, который нужно отформатировать, и нажмите Kutools > Выберите > Выбрать определенные ячейки. Смотрите скриншот:

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

3. Нажмите Ok. Затем появляется диалоговое окно, в котором показано, сколько ячеек было выбрано. Пожалуйста, нажмите OK чтобы закрыть это диалоговое окно.

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

5. Нажмите OK. Теперь выбранные ячейки отформатированы без каких-либо правил условного форматирования. Смотрите скриншот:

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

Очистить весь лист с кодом VBA в Excel

Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!

Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!

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

1. нажмите другой + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.

2. в Microsoft Visual Basic для приложений окна, нажмите Вставить > Модуль, а затем скопируйте ниже код VBA в модуль.

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

Код VBA: очистить весь лист без форматирования ячеек

Если вы хотите очистить и содержимое, и форматирование ячеек, этот код может вам помочь.

Код VBA: очистить весь лист с форматированием ячеек

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

3. нажмите F5 или нажмите кнопку «Выполнить», чтобы запустить код.

Затем указанный рабочий лист с форматированием ячеек или без него немедленно очищается.

Очистить указанный диапазон с кодом VBA в Excel

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

1. Откройте рабочий лист, из которого вы очистите указанный диапазон, затем нажмите другой + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.

2. в Microsoft Visual Basic для приложений окна, нажмите Вставить > Модуль, а затем скопируйте ниже код VBA в модуль.

Очистить диапазон, но сохранить форматирование, попробуйте этот код.

Код VBA: очистить указанный диапазон без форматирования ячеек

Очистить диапазон с форматированием ячеек, используйте этот код:

Код VBA: очистить указанный диапазон с форматированием ячеек

Внимание: A1: C10 - это диапазон, который вы очистите на листе.

3. нажмите F5 ключ или щелкните Запустите кнопку, чтобы очистить диапазон.

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

Формат отображаемого значения

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

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

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

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

Основные свойства объекта Font

Свойство	Описание	Значения
Name	наименование шрифта	«Arial», «Calibri», «Courier New», «Times New Roman» и т.д.
Size	размер шрифта	от 1 до 409 пунктов
Bold	полужирное начертание	True, False
Italic	курсивное начертание	True, False
FontStyle	заменяет Bold и Italic	«обычный», «полужирный», «курсив», «полужирный курсив»
Superscript	надстрочный текст	True, False
Subscript	подстрочный текст	True, False
Underline	подчеркнутый текст	True, False
Color*	цвет текста	от 0 до 16777215

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

Примеры форматирования текста

Пример 1
В этом примере ячейкам диапазона «A1:A3» присвоим шрифты разных наименований:

Читайте также: