Форматирование ячеек Vba Excel

Обновлено: 20.11.2024

Мы можем применить условное форматирование Применение условного форматирования Условное форматирование — это метод в Excel, который позволяет нам форматировать ячейки на листе на основе определенных условий. Его можно найти в разделе стилей на вкладке «Главная». читать больше в ячейку или диапазон ячеек в Excel. Условный формат — это формат, который применяется только к ячейкам, которые соответствуют определенным критериям, например значениям выше определенного значения, положительным или отрицательным значениям или значениям с определенной формулой и т. д. Это условное форматирование также может быть выполнено в программе Excel VBA с использованием "Коллекция условий формата" в макросе/процедуре.

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

FormatConditions.Add/Modify/Delete используется в VBA для добавления/изменения/удаления объектов FormatCondition в коллекцию. Каждый формат представлен объектом FormatCondition. FormatConditions — это свойство объекта Range, и добавьте следующие параметры с синтаксисом ниже:

Синтаксис формулы Добавить имеет следующие аргументы:

Тип: обязательный, указывает, основан ли условный формат на значении, присутствующем в ячейке, или на выражении.
Оператор: необязательный, представляет оператор, который будет использоваться со значением, когда «Тип» основан на значении ячейки.
Формула1: необязательно, представляет значение или выражение, связанное с условным форматом.
Формула2: необязательно, представляет значение или выражение, связанное со второй частью условного формата, когда параметр: «Оператор» имеет значение «xlBetween» или «xlNotBetween».

FormatConditions.Modify также имеет тот же синтаксис, что и FormatConditions.Add.

Ниже приведен список некоторых значений/перечислений, которые могут приниматься некоторыми параметрами «Добавить»/«Изменить»:

Примеры условного форматирования VBA

Ниже приведены примеры условного форматирования в Excel VBA.

Допустим, у нас есть файл Excel, содержащий имена и оценки некоторых учащихся, и мы хотим определить/выделить оценки жирным и синим цветом, что больше 80, а также жирным и красным цветом, что меньше 50. Посмотрим, какие данные содержатся в файле:

Для этого мы используем функцию FormatConditions.Add, как показано ниже:

Код:

Теперь, когда мы запускаем этот код с помощью клавиши F5 или вручную, мы видим, что отметки меньше 50 выделены жирным шрифтом и красным цветом, а отметки больше 80 выделены жирным шрифтом и синим цветом следующим образом:< /p>

Допустим, в приведенном выше примере у нас есть еще один столбец, в котором также указано, что учащийся является «лучшим», если он / она набирает более 80 баллов, в противном случае против него написано «зачет/незачет». Теперь мы хотим выделить значения, указанные как «Topper», жирным и синим цветом. Давайте посмотрим данные, содержащиеся в файле:

В этом случае код/процедура будет работать следующим образом:

Код:

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

Код:

В приведенном выше коде мы видим, что мы хотим проверить, содержит ли диапазон: «C2:C11» строку: «Topper», поэтому параметр: «Onamestor» в «Format.Add» принимает перечисление: Xcontains», чтобы проверить это условие в фиксированном диапазоне (например, C2:C11), а затем выполнить требуемое условное форматирование (изменение шрифта) в этом диапазоне.

Теперь, когда мы запускаем этот код вручную или нажимая клавишу F5, мы видим, что значения ячеек с надписью "Верх" выделяются синим цветом и жирным шрифтом:

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

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

Форматировать по периоду времени

Среднее состояние

Условие цветовой шкалы

Условие IconSet

Состояние панели данных

Уникальные значения

Повторяющиеся значения

10 главных ценностей

Процентное условие

Пробелы и т. д.

При различных тестируемых условиях разные значения/перечисления принимаются параметрами «Добавить».

Что следует помнить об условном форматировании VBA

Метод «Добавить» с «FormatConditions» используется для создания нового условного формата, метод «Удалить» для удаления любого условного формата и метод «Изменить» для изменения любого существующего условного формата.
Метод «Добавить» с «Коллекцией FormatConditions» не работает, если для одного диапазона создано более трех условных форматов.
Чтобы применить более трех условных форматов к диапазону с помощью метода "Добавить", мы можем использовать "Если" или "выбрать регистр".
Если метод "Добавить" имеет параметр "Тип" как "xlExpression", то параметр "Оператор" игнорируется.
Параметры: «Формула1» и «Формула2» в методе «Добавить» могут быть ссылкой на ячейку, постоянным значением, строковым значением или даже формулой.
Параметр: «Формула2» используется только в том случае, если параметр «Оператор» имеет значение «xlBetween» или «xlNotBetween», в противном случае он игнорируется.
Чтобы удалить все условное форматирование с любого рабочего листа, мы можем использовать метод «Удалить» следующим образом:

Форматирование ячеек и диапазонов Excel VBA с помощью средства записи макросов

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

Числовой формат: числовой, дата, научный…
Выравнивание текста: горизонтальное, вертикальное, ориентация…
Управление текстом: перенос текста, сжатие по размеру, объединение ячеек…
Направление текста
Тип шрифта: Calibri, Times new Roman…
Стиль шрифта: обычный, курсив…
Размер шрифта: 8, 9, 10…
Цвет шрифта
Эффекты шрифта: зачеркнутый, верхний индекс…
Границы: стиль, цвет,…
Заливка: цвет, узор…
Защита: заблокирована, скрыта
…

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

Форматирование ячеек и средство записи макросов:

Ниже я объясню, как использовать средство записи макросов, чтобы определить необходимый синтаксис для определенного типа форматирования:

Шаг 1. На вкладке разработчика нажмите кнопку Записать макрос:

Шаг 2. Нажмите "ОК":

Шаг 3. Примените нужное форматирование к диапазону ячеек. В этом примере я изменю цвет диапазона на желтый и применю толстую границу к внешней и внутренней сторонам диапазона:

Шаг 4. Остановите запись макросов

Шаг 6. Откройте базовый визуальный редактор. В окне проекта вы увидите новую созданную папку под названием «Модули». В этой папке появится новый файл «Macro1». Нажав на нее, вы увидите сгенерированный Код:

Для этого примера был сгенерирован код, показанный ниже:

Sub Macro1()
'
' Macro1 Macro
'

'
Range(" I11:P16 ").Выберите
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThick
Конец с
С выделением. Границы(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThick
Заканчиваться
Выбором.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThick
Конец с
С выделением. Границы (xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic < br />.TintAndShade = 0
.Weight = xlThick
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThick
Конец с
С выделением. Границы (xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic < br />.TintAndShade = 0
.Weight = xlThick
Конец
Выделением.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
. Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End Sub

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

Диапазон("I11:P16").Выбрать

Вам придется заменить «I11:P16» диапазоном, который вы пытаетесь изменить. Также вместо использования команды select вы можете напрямую использовать объект диапазона:

Range("I11:P16").Borders(xlDiagonalDown).LineStyle = xlNone
Range("I11:P16").Borders(xlDiagonalUp).LineStyle = xlNone
With Range("I11 :P16").Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThick
End With
>

Я привел несколько примеров ниже.

Форматирование границ ячеек:

В следующем примере создается граница по левому краю ячейки A1. Узор непрерывный и толстый

With Range("A1").Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThick
Конец с

Я подробно рассмотрел эту тему в статье ниже:

Форматирование защиты ячеек:

Следующий пример блокирует и скрывает ячейки A1:

Диапазон("A1").Заблокировано = True
Диапазон("A1").FormulaHidden = True

Форматирование шрифта ячейки:

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

With Range("A1").Font
'тип шрифта
.Name = "Arial"
'стиль шрифта
.FontStyle = "Bold"
'размер шрифта
.Size = 9
'снять зачеркивание
.Strikethrough = True
'снять верхний индекс
.Superscript = False
'снять нижний индекс
.Subscript = False
.OutlineFont = False
.Shadow = False
'выбрать однострочный нижний индекс
.Underline = xlUnderlineStyleSingle
'установить желтый цвет
/>.Color = 65535
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With

Дополнительную информацию о форматировании свойств шрифта см. в статье ниже:

Форматирование выравнивания ячеек:

Следующий код задает горизонтальное выравнивание ячейки A1 по левому краю, выравнивая ее вершины. Проверяет параметры переноса текста и объединения ячеек и применяет ориентацию -45 градусов:

With Range("A1")
'устанавливает выравнивание по горизонтали по левому краю
.HorizontalAlignment = xlLeft
'устанавливает выравнивание по вертикали по ширине
.VerticalAlignment = xlJustify
'отметьте параметр переноса текста
.WrapText = True
'примените ориентацию -45 градусов
.Orientation = -45
'отметьте параметр объединения ячеек
.MergeCells = True
Конец

Дополнительную информацию о выравнивании ячеек см. в статье ниже:

Изменение формата чисел в ячейках:

Следующий код изменяет числовой формат ячеек A1 на дату в формате мм/дд/гг:

Как при переборе ячеек на листе получить настройки формата для ячейки? Потому что, основываясь на этом, я хотел бы построить оператор SQL, чтобы либо добавить отдельные тики, либо нет к полученному значению

Какой формат вы пытаетесь проверить? В Excel собственная функция Cell() предоставляет информацию о форматировании.

4 ответа 4

Похоже, вам нужна функция VarType(). Vartype(Диапазон("A1"))

Хорошо, поэтому вам нужно знать не настройку формата для ячейки, а то, является ли значение числовым. Можете ли вы просто вызвать IsNumeric(Range("A1")) и указать его в кавычках, если False ?

Основываясь на вашем комментарии о том, что некоторые числа хранятся в БД в виде текста, вы не собираетесь решать это с помощью простой формулы. Разве вы не можете просто цитировать значения при построении оператора SQL?

@Remnant: Я пытаюсь сделать вот что. Если ячейка имеет формат числа/десятичного числа, то динамически построенный оператор sql не будет иметь '' вокруг значения, иначе он будет. Так, например: при повторении строк, если в ячейке A1 есть Cat, я бы логически добавил «Cat», но сначала нужно посмотреть, как отформатирован A1. Надеюсь, это имеет смысл

Я бы подумал, что Cell — это функция рабочего листа, которую можно вызвать через Application.WorksheetFunction, но это также не так. Любая помощь очень ценится

Попробуйте использовать в VBA следующее:

Это должно сказать вам, действительно ли значение является текстом или действительно числом, независимо от свойств форматирования ячейки.

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

Я не думаю, что есть какое-либо свойство ячейки, указывающее, действительно ли ячейка содержит числовое значение, хотя VarType() может помочь, но это сложно, потому что Excel допускает числовое форматирование чтобы ячейка содержала строку, а ячейка в текстовом формате — для числовых значений без переопределения свойства NumberFormat.

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

Я не думаю, что формат ячейки важен. Скорее, это тип данных поля в вашей базе данных. Если у вас есть строка 'foobar' в ячейке, и вы создаете инструкцию INSERT INTO sql, которая пытается поместить ее в поле длинного целого числа, она завершится ошибкой независимо от отметок.

И наоборот, если ячейка содержит числовое значение (например, 100), которое необходимо ввести в поле VARCHAR, для него потребуются отметки (например, "100").

Не тот ответ, который вы ищете? Просмотрите другие вопросы с тегом excel vba или задайте свой вопрос.

Связано

Связанные

Горячие вопросы о сети

Чтобы подписаться на этот RSS-канал, скопируйте и вставьте этот URL-адрес в программу для чтения RSS.

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

Что касается объекта диапазона, мы используем свойство Range.NumberFormat для форматирования чисел в диапазоне. В сегодняшней статье мы увидим, как использовать числовой формат в объекте диапазона, чтобы применить стиль форматирования к нашим числам.

Оценка, Hadoop, Excel, мобильные приложения, веб-разработка и многое другое.

Что делает функция числового формата в VBA?

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

Например, предположим, что у вас есть число 43542 в ячейке A2.

Теперь я применю формат даты «дд-ммм-гггг».

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

Как использовать функцию числового формата в VBA?

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

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

Теперь перейдите в редактор VBA и создайте имя макроса.

Код:

Теперь нам нужно указать, какую ячейку на самом деле мы хотим отформатировать, в данном случае нам нужно отформатировать ячейку A2. Поэтому напишите код как «Диапазон («A2»)»

Код:

После выбора ячейки выберите свойство под названием «Числовой формат», поставив точку (.)

После выбора свойства поставьте знак равенства.

Теперь примените формат, который мы хотим применить в данном случае, формат — это формат даты, т. е. формат «дд-ммм-гггг».

Код:

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

Форматирование чисел с использованием встроенных форматов

Предположим, у вас есть несколько чисел от ячейки A1 до A5.

Мы попробуем различные встроенные числовые форматы. Некоторые форматы номеров: «Стандартный», «Общий», «Валюта», «Учет».

Чтобы применить форматирование, нам нужно сначала выбрать диапазон ячеек, здесь диапазон ячеек от A1 до A5, а затем выбрать свойство числового формата.

Примените числовой формат как «Общий».

Код:

Это применит формат валюты, как показано ниже.

Если вы хотите иметь символ валюты, вы можете указать символ валюты непосредственно перед кодом.

Код:

Этот код добавит символ валюты к числам как часть форматирования.

Форматировать процентные значения

Теперь мы увидим, как форматировать процентные значения. Для этого примера я создал некоторые процентные значения из ячеек с A1 по A5.

Теперь выберите диапазон и выберите свойство Числовой формат.

Примените код форматирования «0,00%».

Код:

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

Код:

Запустите этот код с помощью клавиши F5 или вручную, и все отрицательные процентные значения будут выделены красным цветом.

Форматирование положительных и отрицательных чисел

Поскольку мы можем форматировать числа, мы также можем поэкспериментировать с ними. Предположим, что в диапазоне от A1 до A5 есть несколько чисел, которые также включают отрицательные числа.

Код:

Это отформатирует числа следующим образом.

Мы также можем отображать отрицательные числа красным цветом и в квадратных скобках. Для этого ниже приведен код форматирования.

Код:

Запустите этот код, используя клавиши F5 или вручную, и это отформатирует числа следующим образом.

Текст с числовым форматированием

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

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

Код:

Запустите этот код с помощью клавиши F5 или вручную, и это добавит слово «кг» перед всеми числами от B2 до B6.

Форматирование ячеек Vba Excel

Примеры условного форматирования VBA

Что следует помнить об условном форматировании VBA

Рекомендуемые статьи

Форматирование ячеек и диапазонов Excel VBA с помощью средства записи макросов

Форматирование ячеек и средство записи макросов:

Форматирование границ ячеек:

Форматирование защиты ячеек:

Форматирование шрифта ячейки:

Форматирование выравнивания ячеек:

Изменение формата чисел в ячейках:

4 ответа 4

Не тот ответ, который вы ищете? Просмотрите другие вопросы с тегом excel vba или задайте свой вопрос.

Связано

Связанные

Горячие вопросы о сети

Что делает функция числового формата в VBA?

Как использовать функцию числового формата в VBA?

Рекомендуемые статьи