Как изменить цвет фона ячейки vba

В данном примере описаны макросы для автоматического форматирования или спроса формата для ячеек таблиц Excel средствами VBA.

VBA-макрос: заливка, шрифт, линии границ, ширина столбцов и высота строк

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

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

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

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

Чтобы написать свой код макроса откройте специальный VBA-редактор в Excel: «РАЗРАБОТЧИК»-«Код»-«Visual Basic» или нажмите комбинацию клавиш ALT+F11:

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

Sub SbrosFormat()
If TypeName(Selection) <> "Range" Then Exit Sub
With Selection
.HorizontalAlignment = xlVAlignCenter
.VerticalAlignment = xlVAlignCenter
.WrapText = True
.Borders.LineStyle = xlContinuous
.Borders.Weight = xlThin
.Font.ColorIndex = xlColorIndexAutomatic
.Interior.ColorIndex = xlColorIndexAutomatic
.Columns.AutoFit
.Rows.AutoFit
End With
End Sub


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

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



Описание VBA-макроса для формата ячеек таблицы Excel

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

1. Текст в значениях ячеек выравнивается по центру горизонтально и вертикально.
2. Включен построчный перенос текста.
3. Все границы ячеек получают черную обычной толщины непрерывную линию с черным цветом.
4. Сброс цвета шрифта на авто.
5. Удаляется любая заливка ячеек.
6. Ширина столбцов автоматически настраивается под текст в ячейках.
7. Автоматически настроить высоту строк по содержимому ячеек.

Модификация исходного кода макроса для форматирования

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

.HorizontalAlignment = xlHAlignRight.

Таким же образом можно выровнять текст по левую сторону изменив значение константы на xlHAlignLeft. Или можно выровнять положение текста по ширине ячейки используя константу xlHAlignJustify.

Чтобы макрос выравнивал текст в ячейках по вертикали к низу, измените строку кода, отвечающую за данную настройку форматирования. Измените константу, которая присваивается к свойству VerticalAlignment в следующий способ:

Если хотите выровнять текс к верху ячейки, тогда воспользуйтесь константой xlHAlignTop.

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

.Borders.LineStyle = xlDash

.Borders.Color = vbBlue

.Borders.Weight = xlMedium

Описание настройки форматирования для линий границ ячеек. Мы будем получать разные дополнительные типы линий границ если для свойства LineStyle присваивать такие константы:

• xlDoshDot – применяется для рисования пунктирных линий в границах ячеек;
• xlDouble – рисует двойную линию;
• xlHairLine – рисует тонкую линию;
• xlThick – для рисования очень толстой линии.

Для настройки цвета линий Excel предлагает всего 8 констант для определенных цветов. Константы для настройки цвета линий границ для свойства Color:

• vbBlack – черный;
• vbWhite – белый;
• vbRed – красный;
• vbGreen –зеленый;
• vbBlue – синий;
• vbYellow – желтый;
• vbMagenta – алый;
• vbCyan – голубой.

Но при необходимости присвоить линиям границ другие цвета можно вместо константы для свойства Color записать функцию RGB(). Достаточно лишь в аргументе этой функции указать код цвета по шкале от 0 и до 255.

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

.BorderAround xlContinuous, xlMedium, vbBlack

Описание: В первом аргументе для метода BorderAround можно записать также другой стиль линии. Во втором – толщину линии, а в третьем – цвет. Константы, которые можно присвоить в качестве значений для этих аргументов можно использовать те же, которые мы использовали для свойств: LineStyle, Weight, Color.

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

.Rows(1).Font.Bold = True

.Rows(1).Font.Italic = True

.Rows(1).Interior.Color = vbCyan

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

.Columns (1).Font.Bold = True

.Columns (1).Font.Italic = True

.Columns (1).Interior.Color = vbCyan

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

.Rows(.Rows.Count).Font.Bold = True

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

Sub SbrosFormat()
If TypeName(Selection) <> "Range" Then Exit Sub
With Selection
.HorizontalAlignment = xlVAlignCenter
.VerticalAlignment = xlVAlignCenter
.WrapText = True
.Borders.LineStyle = xlDash
.Borders.Color = vbBlue
.Borders.Weight = xlMedium
.Font.ColorIndex = xlColorIndexAutomatic
.Interior.ColorIndex = xlColorIndexAutomatic
.Columns.AutoFit
.Rows.AutoFit
.BorderAround xlContinuous, xlMedium, vbBlack
.Rows(1).Font.Bold = True
.Rows(1).Font.Italic = True
.Rows(1).Interior.Color = vbCyan
.Columns(1).Font.Bold = True
.Columns(1).Font.Italic = True
.Columns(1).Interior.Color = vbCyan
.Rows(.Rows.Count).Font.Bold = True
End With
End Sub


Пример работы измененного кода VBA-макроса:

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

При использовании VBA для кодирования Colorindex (или цвета фона) ячейки полезно знать, какое целое число будет равно какому цвету. Ниже приведено справочное изображение, на котором показан цвет и указан соответствующий ему Colorindex. он же Цветовая палитра VBA

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

Примеры VBA ColorIndex

Установить цвет фона ячейки

Установить цвет шрифта ячейки

Установить цвет границ ячеек

Получить индекс цвета фона ячейки

Установите цвет фона ячейки на цвет другой ячейки

Свойство цвета VBA

Вместо использования свойства ColorIndex Excel / VBA вы можете использовать свойство Color. Свойство Color принимает два типа ввода:

1. vbColor
2. Цвета RGB

Мы обсудим это ниже:

Цвет VB

VB Color — самый простой способ установить цвета в VBA. Однако он также наименее гибкий. Чтобы установить цветовой код с помощью vbColor, используйте таблицу ниже:

Однако, как видно из таблицы, ваши возможности крайне ограничены.

Установить цвет фона ячейки

Установить цвет шрифта ячейки

Установить цвет границ ячеек

Установите цвет фона ячейки на цвет другой ячейки

Цвета RGB

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

Вот пример:

Выше мы установили красный = 255 (максимальное значение), зеленый = 255 (максимальное значение) и синий = 0 (минимальное значение). Это устанавливает желтый цвет фона ячейки.

Вместо этого мы можем установить цвет шрифта ячейки на фиолетовый:

Существует множество онлайн-инструментов для поиска кода RGB для желаемого цвета (вот один).

Список кодов ColorIndex и цвета RGB в Access VBA

Access использует формы для отображения данных. Коды ColorIndex можно использовать для программного изменения цвета фона и цвета переднего плана объектов в формах Access.

Вы поможете развитию сайта, поделившись страницей с друзьями

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

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

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

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

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

2. в Microsoft Visual Basic для приложений окна, скопируйте и вставьте приведенный ниже код VBA в Код: окно.

VBA: изменить цвет ячейки при нажатии на нее

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Target.Interior.Color = vbRed
End Sub
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    Target.Interior.Color = vbGreen
End Sub

3. Нажмите другой + Q клавиши одновременно, чтобы закрыть Microsoft Visual Basic для приложений окно.

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

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

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

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

2. в Microsoft Visual Basic для приложений окна, скопируйте и вставьте приведенный ниже код VBA в Код: окно.

Код VBA: изменить выделенный цвет выбранного диапазона

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    With Target
        .Worksheet.Cells.FormatConditions.Delete
        .FormatConditions.Add xlExpression, , "TRUE"
        .FormatConditions(1).Interior.Color = vbYellow
    End With
End Sub

3. Нажмите другой + Q ключи одновременно, чтобы закрыть Microsoft Visual Basic для приложений окно.

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

Выделите всю строку и столбец активной ячейки с помощью Kutools for Excel

Если вы имеете дело с большим листом, необходимо выделить всю строку и столбец активной ячейки, чтобы облегчить чтение. Здесь Макет для чтения полезности Kutools for Excel могу помочь тебе.

1. Нажмите Кутулс > Макет для чтения. Смотрите скриншот:

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

Внимание: Вы можете изменить настройки макета чтения в зависимости от ваших потребностей, как показано на скриншоте ниже.

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

Выделите всю строку и столбец активной ячейки с помощью Kutools for Excel