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

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

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

Например, можете да създадете правило, което подчертава клетки, които отговарят на определени критерии. Примерите включват:

• Числа, които попадат в определен диапазон (напр. По -малко от 0).
• Първите 10 елемента в списък.
• Създаване на „топлинна карта“.
• Правила „базирани на формули“ за почти всяко условно форматиране.

В Excel условното форматиране може да се намери в лентата под Начало> Стилове (ALT> H> L).

За да създадете свое собствено правило, щракнете върху „Ново правило“ и ще се появи нов прозорец:

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

Всички тези функции за условно форматиране могат да бъдат достъпни с помощта на VBA.

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

Правилата за условно форматиране също се записват при запазване на работния лист

Правилата за условно форматиране се прилагат конкретно за определен работен лист и за определен диапазон от клетки. Ако те са необходими другаде в работната книга, те трябва да бъдат настроени и на този работен лист.

Практически употреби на условно форматиране във VBA

Може да имате голяма част от необработени данни, импортирани във вашия работен лист от CSV (разделени със запетая стойности) файл или от таблица в база данни или заявка. Това може да се влее в таблото за управление или в отчета, с променящи се числа, внесени от един период в друг.

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

Можете да използвате VBA, за да включите или изключите условното форматиране. Можете да използвате VBA, за да изчистите правилата за редица клетки или да ги включите отново. Възможно е да има ситуация, в която има напълно основателна причина за необичаен номер, но когато потребителят представи таблото за управление или докладва на по -високо ниво на управление, той иска да може да премахне „алармените звънчета“.

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

Възможно е също така да имате ситуация, при която има сортиран списък с имена с числови стойности срещу всяко от тях, напр. заплата на служителите, изпитни оценки. С условното форматиране можете да използвате градуирани цветове, за да преминете от най -високо към най -ниско, което изглежда много впечатляващо за целите на презентацията.

Списъкът с имена обаче не винаги ще бъде със статичен размер и можете да използвате VBA код, за да опресните мащаба на градуираните цветове според промените в размера на диапазона.

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

Този пример задава условно форматиране за диапазон от клетки (A1: A10) на работен лист. Ако числото в диапазона е между 100 и 150, тогава цветът на фона на клетката ще бъде червен, в противен случай няма да има цвят.

1234567891011121314

Sub ConditionalFormattingExample ()„Определете диапазонDim MyRange As RangeЗадайте MyRange = Диапазон (“A1: A10”)„Изтриване на съществуващо условно форматиране от диапазонMyRange.FormatConditions.Delete„Прилагане на условно форматиранеMyRange.FormatConditions.Add Тип: = xlCellValue, Оператор: = xl Между, _Формула 1: = "= 100", Формула 2: = "= 150"MyRange.FormatConditions (1) .Interior.Color = RGB (255, 0, 0)End Sub

Забележете, че първо определяме диапазона MyRange за да приложите условно форматиране.

След това изтриваме всяко съществуващо условно форматиране за диапазона. Това е добра идея да предотвратите добавянето на едно и също правило при всяко изпълнение на кода (разбира се, няма да е подходящо при всички обстоятелства).

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

Налични са над 16,7 милиона цвята и с помощта на RGB можете да получите достъп до всички тях. Това е далеч по -лесно, отколкото да се опитваш да си спомниш кое число е с кой цвят. Всеки от трите RGB цветови номера е от 0 до 255.

Обърнете внимание, че параметърът „xlBetween“ е включителен, така че стойностите на клетките от 100 или 150 ще задоволят условието.

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

Може да искате да настроите няколко условни правила във вашия диапазон от данни, така че всички стойности в даден диапазон да бъдат покрити от различни условия:

12345678910111213141516171819

Sub MultipleConditionalFormattingExample ()Dim MyRange As Range'Създаване на обект на диапазонЗадайте MyRange = Диапазон (“A1: A10”)„Изтриване на предишни условни форматиMyRange.FormatConditions.Delete„Добавете първо правилоMyRange.FormatConditions.Add Тип: = xlCellValue, Оператор: = xl Между, _Формула 1: = "= 100", Формула 2: = "= 150"MyRange.FormatConditions (1) .Interior.Color = RGB (255, 0, 0)„Добавете второ правилоMyRange.FormatConditions.Add Тип: = xlCellValue, Оператор: = xlLess, _Формула 1: = "= 100"MyRange.FormatConditions (2) .Interior.Color = vbBlue„Добавете трето правилоMyRange.FormatConditions.Add Тип: = xlCellValue, Оператор: = xlGreater, _Формула 1: = "= 150"MyRange.FormatConditions (3) .Interior.Color = vbYellowEnd Sub

Този пример задава първото правило както преди, с цвят на клетката в червено, ако стойността на клетката е между 100 и 150.

След това се добавят още две правила. Ако стойността на клетката е по -малка от 100, тогава цветът на клетката е син, а ако е по -голям от 150, тогава цветът на клетката е жълт.

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

Ако празните клетки са в този диапазон, те ще се покажат като сини, защото Excel все още ги приема за стойност по -малка от 100.

Пътят около това е да добавите друго условие като израз. Това трябва да се добави като първото правило на условията в кода. Много е важно, когато има множество правила, да се получи правилния ред на изпълнение, в противен случай резултатите могат да бъдат непредсказуеми.

1234567891011121314151617181920212223

Sub MultipleConditionalFormattingExample ()Dim MyRange As Range'Създаване на обект на диапазонЗадайте MyRange = Диапазон (“A1: A10”)„Изтриване на предишни условни форматиMyRange.FormatConditions.Delete„Добавете първо правилоMyRange.FormatConditions.Add Type: = xlExpression, Formula1: = _"= LEN (TRIM (A1)) = 0"MyRange.FormatConditions (1) .Interior.Pattern = xlNone„Добавете второ правилоMyRange.FormatConditions.Add Тип: = xlCellValue, Оператор: = xl Между, _Формула 1: = "= 100", Формула 2: = "= 150"MyRange.FormatConditions (2) .Interior.Color = RGB (255, 0, 0)„Добавете трето правилоMyRange.FormatConditions.Add Тип: = xlCellValue, Оператор: = xlLess, _Формула 1: = "= 100"MyRange.FormatConditions (3) .Interior.Color = vbBlue„Добавете четвърто правилоMyRange.FormatConditions.Add Тип: = xlCellValue, Оператор: = xlGreater, _Формула 1: = "= 150"MyRange.FormatConditions (4) .Interior.Color = RGB (0, 255, 0)End Sub

Това използва типа xlExpression и след това използва стандартна формула на Excel, за да определи дали клетката е празна, вместо числова стойност.

Обектът FormatConditions е част от обекта Range. Той действа по същия начин като колекция с индекс, започващ от 1. Можете да повторите този обект, като използвате For… Next или For… Всеки цикъл.

Изтриване на правило

Понякога може да се наложи да изтриете отделно правило в набор от множество правила, ако то не отговаря на изискванията за данни.

12345678910111213

Sub DeleteConditionalFormattingExample ()Dim MyRange As Range'Създаване на обект на диапазонЗадайте MyRange = Диапазон (“A1: A10”)„Изтриване на предишни условни форматиMyRange.FormatConditions.Delete„Добавете първо правилоMyRange.FormatConditions.Add Тип: = xlCellValue, Оператор: = xl Между, _Формула 1: = "= 100", Формула 2: = "= 150"MyRange.FormatConditions (1) .Interior.Color = RGB (255, 0, 0)„Изтриване на правилоMyRange.FormatConditions (1). ИзтриванеEnd Sub

Този код създава ново правило за диапазон A1: A10 и след това го изтрива. Трябва да използвате правилния номер на индекса за изтриването, така че проверете „Управление на правилата“ в интерфейса на Excel (това ще покаже правилата по реда на изпълнение), за да сте сигурни, че ще получите правилния номер на индекса. Обърнете внимание, че няма възможност за отмяна в Excel, ако изтриете правило за условно форматиране във VBA, за разлика от това, ако го направите чрез интерфейса на Excel.

Промяна на правило

Тъй като правилата са колекция от обекти, базирани на определен диапазон, можете лесно да правите промени в определени правила, използвайки VBA. Действителните свойства, след като правилото е добавено, са само за четене, но можете да използвате метода Modify, за да ги промените. Свойства като цветове се четат / пишат.

123456789101112131415

Sub ChangeConditionalFormattingExample ()Dim MyRange As Range'Създаване на обект на диапазонЗадайте MyRange = Диапазон (“A1: A10”)„Изтриване на предишни условни форматиMyRange.FormatConditions.Delete„Добавете първо правилоMyRange.FormatConditions.Add Тип: = xlCellValue, Оператор: = xl Между, _Формула 1: = "= 100", Формула 2: = "= 150"MyRange.FormatConditions (1) .Interior.Color = RGB (255, 0, 0)„Промяна на правилотоMyRange.FormatConditions (1). Промяна на xlCellValue, xlLess, "10"„Промяна на цвета на правилотоMyRange.FormatConditions (1) .Interior.Color = vbGreenEnd Sub

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

След това кодът променя правилото на числа по -малки от 10. Ако условието е вярно, цветът на клетката сега се променя на зелен.

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

Условното форматиране на Excel има възможност за използване на градуирани цветове в диапазон от числа, изпълняващи се във възходящ или низходящ ред.

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

1234567891011121314151617181920212223242526272829

Sub GraduatedColors ()Dim MyRange As Range'Създаване на обект на диапазонЗадайте MyRange = Диапазон (“A1: A10”)„Изтриване на предишни условни форматиMyRange.FormatConditions.Delete'Определете тип скалаMyRange.FormatConditions.AddColorScale ColorScaleType: = 3'Изберете цвят за най -ниската стойност в диапазонаMyRange.FormatConditions (1) .ColorScaleCriteria (1) .Type = _xlConditionValueLowestValueС MyRange.FormatConditions (1) .ColorScaleCriteria (1). FormatColor.Цвят = 7039480Край с'Изберете цвят за средните стойности в диапазонаMyRange.FormatConditions (1) .ColorScaleCriteria (2) .Type = _xlConditionValuePercentileMyRange.FormatConditions (1) .ColorScaleCriteria (2) .Value = 50'Изберете цвета за средната точка на диапазонаС MyRange.FormatConditions (1) .ColorScaleCriteria (2). FormatColor.Цвят = 8711167Край с'Изберете цвят за най -високата стойност в диапазонаMyRange.FormatConditions (1) .ColorScaleCriteria (3) .Type = _xlConditionValueHighestValueС MyRange.FormatConditions (1) .ColorScaleCriteria (3). FormatColor.Цвят = 8109667Край сEnd Sub

Когато този код се изпълнява, той ще завърши цветовете на клетките според възходящите стойности в диапазона A1: A10.

Това е много впечатляващ начин за показване на данните и със сигурност ще привлече вниманието на потребителите.

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

Когато имате огромно количество данни, лесно може да пропуснете стойност на грешка в различните си работни листове. Ако това бъде представено на потребител, без да бъде разрешено, това може да доведе до големи проблеми и потребителят да загуби доверие в числата. Това използва тип правило на xlExpression и функция на Excel на IsError за оценка на клетката.

Можете да създадете код, така че всички клетки с грешки да имат цвят на клетката в червено:

1234567891011

Sub ErrorConditionalFormattingExample ()Dim MyRange As Range'Създаване на обект на диапазонЗадайте MyRange = Диапазон (“A1: A10”)„Изтриване на предишни условни форматиMyRange.FormatConditions.Delete„Добавяне на правило за грешкаMyRange.FormatConditions.Add Type: = xlExpression, Formula1: = "= IsError (A1) = true"'Задайте цвета на интериора на червеноMyRange.FormatConditions (1) .Interior.Color = RGB (255, 0, 0)End Sub

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

Може да имате импортирани данни, където искате да подчертаете дати, които са в миналото. Пример за това може да бъде доклад на длъжници, в който искате всички дати на фактура над 30 дни да се открояват.

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

1234567891011

Sub DateInPastConditionalFormattingExample ()Dim MyRange As Range„Създайте обект на диапазон въз основа на колона с датиЗадайте MyRange = Диапазон (“A1: A10”)„Изтриване на предишни условни форматиMyRange.FormatConditions.Delete„Добавете правило за грешка за минали датиMyRange.FormatConditions.Add Тип: = xlExpression, Формула1: = "= Сега ()-A1> 30"'Задайте цвета на интериора на червеноMyRange.FormatConditions (1) .Interior.Color = RGB (255, 0, 0)End Sub

Този код ще отнеме диапазон от дати в диапазона A1: A10 и ще зададе цвета на клетката в червено за всяка дата, която е над 30 дни в миналото.

Във формулата, използвана в условието, Now () дава текущата дата и час. Това ще продължи да се преизчислява всеки път, когато работният лист се преизчислява, така че форматирането ще се промени от един ден на следващия.

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

Можете да използвате VBA, за да добавите ленти с данни към диапазон от числа. Те са почти като мини диаграми и дават незабавен поглед върху това колко големи са числата един спрямо друг. Приемайки стойностите по подразбиране за лентите с данни, кодът е много лесен за писане.

123456

Sub DataBarFormattingExample ()Dim MyRange As RangeЗадайте MyRange = Диапазон (“A1: A10”)MyRange.FormatConditions.DeleteMyRange.FormatConditions.AddDatabarEnd Sub

Вашите данни ще изглеждат така в работния лист:

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

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

12345678910111213141516171819202122232425

Sub IconSetsExample ()Dim MyRange As Range'Създаване на обект на диапазонЗадайте MyRange = Диапазон (“A1: A10”)„Изтриване на предишни условни форматиMyRange.FormatConditions.Delete„Добавяне на набор от икони към обекта FormatConditionsMyRange.FormatConditions.AddIconSetCondition'Задайте иконата на стрелки - условие 1С MyRange.FormatConditions (1).IconSet = ActiveWorkbook.IconSets (xl3Arrows)Край с'задайте критериите за икони за необходимата процентна стойност - условие 2С MyRange.FormatConditions (1) .IconCriteria (2).Type = xlConditionValuePercent.Цена = 33.Оператор = xlGreaterEqualКрай с'задайте критериите за иконата за необходимата процентна стойност - условие 3С MyRange.FormatConditions (1) .IconCriteria (3).Type = xlConditionValuePercent.Значение = 67.Оператор = xlGreaterEqualКрай сEnd Sub

Това ще даде незабавен изглед, показващ дали числото е високо или ниско. След като стартирате този код, вашият работен лист ще изглежда така:

Използване на условно форматиране за подчертаване на първите пет

Можете да използвате VBA код, за да маркирате първите 5 числа в диапазона от данни. Използвате параметър, наречен „AddTop10“, но можете да регулирате номера на ранга в кода на 5. Потребителят може да пожелае да види най -високите числа в диапазон, без да се налага първо да сортира данните.

1234567891011121314151617181920212223

Sub Top5Example ()Dim MyRange As Range'Създаване на обект на диапазонЗадайте MyRange = Диапазон (“A1: A10”)„Изтриване на предишни условни форматиMyRange.FormatConditions.Delete„Добавете условие Top10MyRange.FormatConditions.AddTop10С MyRange.FormatConditions (1)'Параметър от горе до долу.TopBottom = xlTop10Top„Само топ 5. Ранг = 5Край сС MyRange.FormatConditions (1). Шрифт'Задайте цвета на шрифта.Цвят = -16383844Край сС MyRange.FormatConditions (1) .Интериор'Задайте цвета на фона на клетката.Цвят = 13551615Край сEnd Sub

Данните във вашия работен лист ще изглеждат така след стартиране на кода:

Имайте предвид, че стойността 145 се появява два пъти, така че шест клетки са маркирани.

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

StopIfTrue е от значение, ако диапазон от клетки има множество правила за условно форматиране. Една единична клетка в диапазона може да отговаря на първото правило, но може да отговаря и на следващите правила. Като разработчик може да искате да показва форматирането само за първото правило, до което се стига. Други критерии за правила могат да се припокриват и могат да направят непреднамерени промени, ако им се позволи да продължат надолу по списъка с правила.

По подразбиране за този параметър е True, но можете да го промените, ако искате да се вземат предвид всички останали правила за тази клетка:

1	MyRange. FormatConditions (1) .StopIfTrue = False

Параметърът SetFirstPriority диктува дали това правило на условието ще бъде оценено първо, когато има няколко правила за тази клетка.

1	MyRange. FormatConditions (1). SetFirstPriority

Това премества позицията на това правило на позиция 1 в колекцията от условия за форматиране и всички други правила ще бъдат преместени надолу с променени номера на индекса. Внимавайте, ако правите някакви промени в правилата в кода, като използвате индексните номера. Трябва да се уверите, че променяте или изтривате правилното правило.

Можете да промените приоритета на правило:

1	MyRange. FormatConditions (1). Приоритет = 3

Това ще промени относителните позиции на всички други правила в списъка с условен формат.

Използване на условно форматиране, отнасящо се до други стойности на клетката

Това е едно нещо, което условното форматиране на Excel не може да направи. Можете обаче да създадете свой собствен VBA код, за да направите това.

Да предположим, че имате колона с данни и в съседната клетка към всяко число има някакъв текст, който показва какво форматиране трябва да се извърши на всяко число.

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

123456789101112131415161718192021

Sub ReferToAnotherCellForConditionalFormatting ()„Създайте променливи, за да съхранявате броя редове за табличните данниDim RRow As Long, N As Long'Уловете броя редове в табличния диапазон от данниRRow = ActiveSheet.UsedRange.Rows.Count„Итерация през всички редове в табличния диапазон от данниЗа N = 1 За RRow„Използвайте оператор Select Case, за да оцените форматирането въз основа на колона 2Изберете Case ActiveSheet.Cells (N, 2) .Value„Превърнете цвета на интериора в синКалъф "син"ActiveSheet.Cells (N, 1) .Interior.Color = vbBlue„Превърнете цвета на интериора в червенКалъф "червен"ActiveSheet.Cells (N, 1) .Interior.Color = vbRed'Превърнете цвета на интериора в зеленКалъф "Зелен"ActiveSheet.Cells (N, 1) .Interior.Color = vbGreenКрай ИзберетеСледва NEnd Sub

След като този код бъде пуснат, вашият работен лист ще изглежда така:

Клетките, посочени за форматиране, могат да бъдат навсякъде в работния лист или дори на друг работен лист в работната книга. Можете да използвате каквато и да е форма на текст, за да направите условие за форматирането, и сте ограничени само от въображението си в употребите, към които можете да поставите този код.

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

Както видяхте в предишните примери, операторите се използват, за да определят как ще се оценяват стойностите на условията, напр. xlМежду тях.

Има няколко от тези оператори, които могат да се използват, в зависимост от това как искате да посочите критериите на правилата си.

Име	Стойност	Описание
xlМежду тях	1	Между. Може да се използва само ако са предоставени две формули.
xl Равен	3	Равен.
xlGreater	5	По-голяма от.
xlGreaterEqual	7	По -голямо или равно на.
xLLess	6	По-малко от.
xlLessEqual	8	По -малко или равно на.
xlNotBetween	2	Не между. Може да се използва само ако са предоставени две формули.
xlNotEqual	4	Не е равно.