Excel VBA로 조건부 서식 사용하기

Excel 조건부 서식

Excel 조건부 서식을 사용하면 셀 서식을 결정하는 규칙을 정의할 수 있습니다.

예를 들어 특정 기준을 충족하는 셀을 강조 표시하는 규칙을 만들 수 있습니다. 다음 예시를 참조해주세요:

• 특정 범위에 속하는 숫자(예: 0 미만).
• 목록에서 상위 10개 항목.
• 히트 맵’ 만들기.
• 실제로 가장 많이 상요되는 수식에 기반한 조건부 서식

Excel에서 조건부 서식은 홈 > 스타일(ALT > H > L)에 있습니다.

나만의 규칙을 만들려면 ‘새 규칙’을 클릭하면 새 창이 나타납니다:

VBA의 조건부 서식

모든 조건부 서식 지정 기능은 VBA에서도 이용 가능합니다.

VBA 코드 내에서 조건부 서식을 설정하면 새 매개변수가 Excel 조건부 서식 창에 나타나고 사용자가 볼 수 있다는 점에 유의하세요. 워크시트를 잠그지 않는 한 사용자는 이러한 매개변수를 편집하거나 삭제할 수 있습니다.

워크시트를 저장할 때 조건부 서식 지정 규칙도 저장됩니다.

조건부 서식 지정 규칙은 특정 워크시트와 특정 범위의 셀에 특별히 적용됩니다. 통합 문서의 다른 곳에서도 필요한 경우 해당 워크시트에도 조건부 서식을 설정해야 합니다.

 

VBA에서 조건부 서식의 실제 사용 사례

CSV (쉼표로 구분된 값) 파일이나 데이터베이스 테이블 또는 쿼리에서 워크시트로 가져온 대량의 로우 데이터를 가지고 있을 수 있습니다. 이 데이터는 대시보드 또는 보고서에 사용될 수 있습니다. 기간에 따라 변경되는 숫자가 있다고 가정하곘습니다. 숫자가 변경될 수 있습니다.

숫자가 변경되어 허용 범위를 벗어나는 경우 셀의 배경색 등을 빨간색으로 강조하고 싶을 수 있으며, 조건부 서식을 설정하여 이를 수행할 수 있습니다. 이렇게 하면 사용자는 이 숫자에 쉽게 주목하고 왜 이런 일이 발생하는지 조사할 수 있습니다.

VBA를 사용하여 조건부 서식을 켜거나 끌 수 있습니다. VBA를 사용하여 셀 범위의 규칙을 지우거나 다시 설정할 수 있습니다. 비정상적인 숫자에 대해 완벽하게 타당한 이유가 있지만, 사용자가 대시보드 또는 보고서를 더 상사에게 제시할 때 조건부 서식으로 만들어진 눈에 띄는 강조표시를 제거하기를 원하는 상황이 있을 수 있습니다.

또한 가져온 원시 데이터에서 숫자가 터무니없이 크거나 터무니없이 작은 부분을 강조 표시하고 싶을 수도 있습니다. 가져온 데이터 범위는 일반적으로 각 기간마다 크기가 다르므로 VBA를 사용하여 새 데이터 범위의 크기를 평가하고 해당 범위에 대해서만 조건부 서식을 삽입할 수 있습니다.

직원 급여, 시험 점수와 같이 각 항목에 대해 숫자 값이 있는 정렬된 이름 목록이 있는 상황도 있을 수 있습니다. 조건부 서식을 사용하면 가장 높은 값에서 가장 낮은 값 사이의 데이터들을 색상으로 표현할 수 있어서 프레젠테이션용으로 매우 인상적입니다.

그러나 데이터의 사이즈가 항상 고정되어 있는 것은 아닙니다. VBA 코드를 사용하여 범위의 크기 변경에 따라 색상을 표시하기 위한 범위를 새로 고칠 수 있습니다.

 

범위에 조건부 서식을 만드는 간단한 예제

이 예에서는 워크시트의 셀 범위(A1:A10)에 조건부 서식을 설정합니다. 범위의 숫자가 100에서 150 사이이면 셀 배경색이 빨간색이 되고, 그렇지 않으면 색이 없습니다.

Sub ConditionalFormattingExample()

'범위 변수를 정의합니다.
Dim MyRange As Range
Set MyRange = Range("A1:A10")

'정의된 범위에서 기존 조건부 서식 삭제
MyRange.FormatConditions.Delete

'조건부 서식을 적용합니다.
MyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
        Formula1:="=100", Formula2:="=150"
MyRange.FormatConditions(1).Interior.Color = RGB(255, 0, 0)
End Sub

먼저 조건부 서식을 적용하기 위해 MyRange 범위 변수를 정의한 것을 알 수 있습니다.

다음으로 범위에 대한 기존 조건부 서식을 삭제합니다. 이렇게 하면 코드를 실행할 때마다 동일한 규칙이 추가되는 것을 방지할 수 있습니다(물론 모든 상황에 적합하지는 않습니다).

색상은 숫자 값으로 지정됩니다. 이를 위해 RGB(빨강, 초록, 파랑) 표기법을 사용하는 것이 좋습니다. 표준 색상 상수(예: vbRed, vbBlue)를 사용할 수 있지만 선택 가능한 색상은 8개로 제한됩니다.

사용 가능한 색상은 1,670만 개가 넘으며 RGB를 사용하면 모든 색상을 사용할 수 있습니다. 어떤 번호가 어떤 색상과 어울리는지 기억하는 것보다 훨씬 쉽습니다. 세 가지 RGB 색상 번호는 각각 0에서 255까지입니다.

위 예제에서 xlBetween’ 매개변수는 100 또는 150의 셀 값을 포함하여 조건부 서식을 적용합니다.

다중 조건 서식

여러 조건부 규칙을 설정하여 데이터 범위의 모든 값에 서로 다른 조건이 적용되도록 할 수 있습니다:

Sub MultipleConditionalFormattingExample()
Dim MyRange As Range
'Range 객체를 생성합니다
Set MyRange = Range("A1:A10")
'이전에 생성된 조건부서식을 삭제합니다
MyRange.FormatConditions.Delete
'첫번째 규칙을 추가합니다.
MyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
        Formula1:="=100", Formula2:="=150"
MyRange.FormatConditions(1).Interior.Color=RGB(255, 0, 0)
'두번째 규칙을 추가합니다.
MyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
        Formula1:="=100"
MyRange.FormatConditions(2).Interior.Color=vbBlue
'세번째 규칙을 추가합니다.
MyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
        Formula1:="=150"
MyRange.FormatConditions(3).Interior.Color=vbYellow
End Sub

이 예제에서는 셀 값이 100에서 150 사이인 경우 셀 색상을 빨간색으로 설정하는 첫 번째 규칙을 이전예제와 같이 설정합니다.

그런 다음 두 개의 규칙이 더 추가됩니다. 셀 값이 100보다 작으면 셀 색상이 파란색이 되고 150보다 크면 셀 색상이 노란색이 되는 규칙입니다.

규칙에 대한 경우의 수를 모두 고려하고 규칙이 겹치지 않도록 해야 합니다.

빈 셀이 이 범위에 있으면 Excel에서는 여전히 100보다 작은 값을 갖는 것으로 간주하므로 파란색으로 표시됩니다.

이 문제를 해결하는 방법은 다른 조건을 추가하는 것입니다. 코드 내에서 첫 번째 순서에 추가해야 합니다. 여러 규칙이 있는 경우 실행 순서를 올바르게 지정하는 것이 매우 중요하며, 그렇지 않으면 예측하지 못한 결과를 얻을 수 있습니다.

Sub MultipleConditionalFormattingExample()
Dim MyRange As Range
'Range 객체를 생성합니다.
Set MyRange = Range("A1:A10")
'이전에 생성된 조건부 서식을 삭제합니다.
MyRange.FormatConditions.Delete
'첫번째 규칙을 추가합니다.
MyRange.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=LEN(TRIM(A1))=0"
MyRange.FormatConditions(1).Interior.Pattern=xlNone
'두번째 규칙을 추가합니다.
MyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _ 
        Formula1:="=100", Formula2:="=150"
MyRange.FormatConditions(2).Interior.Color=RGB(255, 0, 0)
'세번째 규칙을 추가합니다.
MyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
        Formula1:="=100"
MyRange.FormatConditions(3).Interior.Color=vbBlue
'네번째 규칙을 추가합니다.
MyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
        Formula1:="=150"
MyRange.FormatConditions(4).Interior.Color = RGB(0, 255, 0)
End Sub

이 예제에서는 xlExpression 유형을 사용한 다음 표준 Excel 수식을 사용하여 셀이 공백인지 여부를 확인합니다.

FormatConditions 객체는 Range 객체의 일부입니다. 인덱스가 1부터 시작하는 컬렉션과 동일한 방식으로 작동합니다. 이 객체를 For…Next 또는 For…Each 반복문을 사용하여 반복할 수 있습니다.

 

규칙 삭제하기

데이터 요구 사항에 맞지 않는 경우 여러 규칙 집합내의 개별 규칙을 삭제해야 하는 경우가 있습니다.

Sub DeleteConditionalFormattingExample()
Dim MyRange As Range
'Range객체를 생성합니다.
Set MyRange = Range("A1:A10")
'이전 조건부 서식을 삭제합니다.
MyRange.FormatConditions.Delete
'첫번째 규칙을 생성합니다.
MyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
        Formula1:="=100", Formula2:="=150"
MyRange.FormatConditions(1).Interior.Color = RGB(255, 0, 0)
'규칙을 삭제합니다.
MyRange.FormatConditions(1).Delete
End Sub

이 코드는 A1:A10 범위에 대한 새 규칙을 만든 다음 삭제합니다. 삭제할 때 올바른 인덱스 번호를 사용해야 하므로 Excel의 ‘규칙 관리’를 확인하여(실행 순서대로 규칙이 표시됨) 올바른 인덱스 번호를 얻었는지 확인하세요. VBA에서 조건부 서식 지정 규칙을 삭제하는 경우 Excel에서와 다르게 실행 취소 기능이 없습니다. 이 점을 유의해 주세요.

 

규칙 변경하기

규칙은 지정된 범위를 기반으로 하는 객체의 모음이므로 VBA을 통해서 특정 규칙을 쉽게 변경할 수 있습니다. 규칙이 추가되면 실제 속성은 읽기 전용이지만 Modify 메서드를 사용하여 변경할 수 있습니다. 색상과 같은 속성은 읽기/쓰기가 가능합니다.

Sub ChangeConditionalFormattingExample()
Dim MyRange As Range
'Range 객체를 생성합니다.
Set MyRange = Range("A1:A10")
'이전 조건부 서식을 삭제합니다.
MyRange.FormatConditions.Delete
'첫번째 규칙을 추가합니다.
MyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
        Formula1:="=100", Formula2:="=150"
MyRange.FormatConditions(1).Interior.Color = RGB(255, 0, 0)
'규칙 적용 색상을 변경합니다.
MyRange.FormatConditions(1).Modify xlCellValue, xlLess, "10"
'규칙을 변경합니다.
MyRange.FormatConditions(1).Interior.Color=vbGreen
End Sub

이 코드는 범위 개체(A1:A10)를 생성하고 100에서 150 사이의 숫자에 대한 규칙을 추가합니다. 조건이 참이면 셀 색상을 빨간색으로 변경합니다.

그런 다음 10보다 작은 숫자로 규칙을 변경합니다. 조건이 참이면 이제 셀 색상을 녹색으로 변경합니다.

그라데이션 색조 사용하기

Excel 조건부 서식에는 오름차순 또는 내림차순으로 구분할 수 있는 그라데이션 색조 기능이 있습니다.

이는 지역별 판매량, 도시 온도 또는 도시 간 거리와 같은 데이터가 있는 경우 매우 유용합니다. VBA를 사용하면 Excel에서 제공하는 표준 색상이 아닌 자신만의 그라데이션 색조를 선택할 수 있다는 이점이 있습니다.

Sub GraduatedColors()
Dim MyRange As Range
'Range 객체를 생성합니다.
Set MyRange = Range("A1:A10")
'이전에 생성된 조건부 서식을 삭제합니다.
MyRange.FormatConditions.Delete
'색조 유형을 정의합니다.
    MyRange.FormatConditions.AddColorScale ColorScaleType:=3
    '범위에서 가장 낮은 값에 대한 색상을 선택합니다.
    MyRange.FormatConditions(1).ColorScaleCriteria(1).Type = _
        xlConditionValueLowestValue
    With MyRange.FormatConditions(1).ColorScaleCriteria(1).FormatColor
        .Color = 7039480
    End With
    '범위의 중간 값에 대한 색상을 선택합니다.
    MyRange.FormatConditions(1).ColorScaleCriteria(2).Type = _
        xlConditionValuePercentile
    MyRange.FormatConditions(1).ColorScaleCriteria(2).Value = 50
    With MyRange.FormatConditions(1).ColorScaleCriteria(2).FormatColor
        .Color = 8711167
    End With
     '범위에서 가장 큰 값의 색상을 선택합니다.
    MyRange.FormatConditions(1).ColorScaleCriteria(3).Type = _
        xlConditionValueHighestValue
    With MyRange.FormatConditions(1).ColorScaleCriteria(3).FormatColor
        .Color = 8109667
    End With
End Sub

이 코드를 실행하면 A1:A10 범위의 오름차순 값에 따라 셀 색상이 그라데이션됩니다.

그라데이션 색상은 데이터를 강조하기 위한 효과적인 방법이고 사용자가 주의깊게 볼 수 있도록 해줍니다.

 

오류 값에 대한 조건부 서식 지정하기

데이터 양이 방대하면 다양한 워크시트에서 오류 값을 쉽게 놓칠 수 있습니다. 이러한 오류가 해결되지 않은 채 사용자에게 표시되면 큰 문제가 발생하고 사용자가 수치에 대한 신뢰를 잃을 수 있습니다. 이 기능은 규칙 유형인 xlExpression과 Excel 함수인 IsError를 사용하여 셀에 규칙을 적용합니다.

오류가 있는 모든 셀의 셀 색상이 빨간색이 되도록 코드를 만들 수 있습니다:

Sub ErrorConditionalFormattingExample()
Dim MyRange As Range
'Range 객체를 생성합니다.
Set MyRange = Range("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 함수를 사용하여 날짜에 대한 규칙을 생성합니다.

Sub DateInPastConditionalFormattingExample()
Dim MyRange As Range
'날짜로 이루어진 열에 대한 Range 객체를 생성합니다.
Set MyRange = Range("A1:A10")
'이전에 생성된 조건부 서식을 삭제합니다.
MyRange.FormatConditions.Delete
'과거 날짜에 대한 규칙을 생성합니다.
MyRange.FormatConditions.Add Type:=xlExpression、Formula1:="=Now()-A1 > 30"
'셀 색상을 빨간색으로 변경합니다.
MyRange.FormatConditions(1).Interior.Color=RGB(255、0、0)
End Sub

이 코드는 A1:A10 범위의 날짜 데이터에 대해서 현재 기준  30일이 지난 날짜에 대해 셀 색상을 빨간색으로 설정합니다.

조건에 사용되는 수식에서 Now()는 현재 날짜와 시간을 제공합니다. 워크시트를 다시 계산할 때마다 다시 계산되므로 날짜가 바뀌면 서식이 변경됩니다.

VBA 조건부 서식에서 데이터 막대 사용하기

VBA를 사용하여 다양한 숫자에 데이터 막대를 추가할 수 있습니다. 이는 미니 차트와 거의 비슷하며 숫자가 서로 얼마나 큰지 즉시 확인할 수 있습니다. 데이터 막대의 기본값을 사용하면 코드를 작성하기가 간단합니다.

Sub DataBarFormattingExample()
Dim MyRange As Range
Set MyRange = Range("A1:A10")
MyRange.FormatConditions.Delete
MyRange.FormatConditions.AddDatabar
End Sub

워크시트에서 데이터가 다음과 같이 표시됩니다:

VBA 조건부 서식에서 아이콘 사용하기

조건부 서식을 사용하여 워크시트에서 숫자 옆에 아이콘을 배치할 수 있습니다. 아이콘은 화살표, 원 또는 기타 다양한 모양이  있습니다. 이 예제에서는 백분율 값에 따라 숫자에 화살표 아이콘을 추가합니다:

Sub IconSetsExample()
Dim MyRange As Range
'Range 객체를 생성합니다.
Set MyRange = Range("A1:A10")
'이전에 생성된 조건부 서식을 삭제합니다.
MyRange.FormatConditions.Delete
'FormatConditions 객체에 아이콘 집합을 추가합니다.
MyRange.FormatConditions.AddIconSetCondition
'조건1 - 아이콘 세트를 화살표로 설정합니다.
With MyRange.FormatConditions(1)
    .IconSet = ActiveWorkbook.IconSets(xl3Arrows)
End With
'조건2 - 필요한 백분율 값에 대한 아이콘 기준을 설정합니다.
With MyRange.FormatConditions(1).IconCriteria(2)
    .Type = xlConditionValuePercent
    .Value = 33
    .Operator = xlGreaterEqual
End With
'조건3 - 필요한 백분율 값에 대한 아이콘 기준을 설정합니다.
With MyRange.FormatConditions(1).IconCriteria(3)
    .Type = xlConditionValuePercent
    .Value = 67
    .Operator = xlGreaterEqual
End With
End Sub

이렇게 하면 숫자가 높은지 낮은지를 즉시 볼 수 있습니다. 이 코드를 실행하면 워크시트에 다음과 같이 표시됩니다:

조건부 서식을 사용하여 상위 5개 데이터에 강조 표시하기

VBA 코드를 사용하여 데이터 범위 내에서 상위 5개 숫자를 강조 표시할 수 있습니다. ‘AddTop10’이라는 매개 변수를 사용하지만 코드 내에서 순위 숫자를 5로 조정할 수 있습니다. 사용자는 데이터를 먼저 정렬하지 않고도 범위 내에서 가장 높은 숫자를 볼 수 있습니다.

Sub Top5Example()
Dim MyRange As Range
'Range 객체를 생성합니다.
Set MyRange = Range("A1:A10")
'이전에 생성된 조건부 서식을 삭제합니다.
MyRange.FormatConditions.Delete
    '상위 10개 데이터를 추출하는 조건을 추가합니다.
    MyRange.FormatConditions.AddTop10
    With MyRange.FormatConditions(1)
        '내림차순으로 순서를 정하도록 설정합니다.
        .TopBottom = xlTop10Top
        '상위 5개까지만 설정
        .Rank = 5
    End With
    With MyRange.FormatConditions(1).Font
        '글꼴 색상을 설정합니다.
        .Color = -16383844
    End With
    With MyRange.FormatConditions(1).Interior
        '셀 배경색을 설정합니다.
        .Color = 13551615
    End With
End Sub

코드를 실행하면 워크시트의 데이터는 다음과 같이 표시됩니다:

145가 두 개 있기 때문에 6개의 셀이 강조 표시됩니다.

 

중요 매개 변수 : StopIfTrue 및 SetFirstPriority

셀 범위에 여러 조건부 서식 지정 규칙이 있을 때 StopIfTrue 매개 변수를 활용할 수 있습니다. 범위 내의 단일 셀이 첫 번째 규칙을 만족할 수도 있지만 후속 규칙도 만족할 수 있습니다. 개발자는 첫 번째 규칙에 대해서만 서식을 표시하기를 원할 수 있습니다. 다른 규칙 기준이 겹칠 경우 후순위 규칙이 적용되도록 허용하면 의도하지 않은 변경이 발생할 수 있습니다.

이 매개 변수의 기본값은 True이지만 해당 셀에 대한 다른 모든 규칙이 적용되는 것을 원할 경우 아래와 같이 변경할 수 있습니다:

MyRange.MyRange.FormatConditions(1).StopIfTrue = False

SetFirstPriority 매개 변수는 해당 셀에 대한 여러 규칙이 있을 때 해당 조건 규칙을 먼저 평가할지 여부를 지정합니다.

MyRange.FormatConditions(1).SetFirstPriority

이렇게 하면 해당 규칙의 위치가 조건부 서식 컬렉션 내에서 첫 번째 위치로 이동하고 다른 규칙은 그 아래로 이동합니다. 인덱스 번호를 사용하여 코드에서 규칙을 변경하는 경우 주의하세요. 올바른 규칙을 변경하거나 삭제하고 있는지 확인해야 합니다.

규칙의 우선순위를 변경할 수 있습니다:

MyRange.FormatConditions(1).Priority=3

이렇게 하면 조건부 서식 목록 내의 다른 규칙의 상대적 위치가 변경됩니다.

 

다른 셀 값을 참조하는 조건부 서식 사용하기

이것은 Excel 조건부 서식으로는 할 수 없는 일 중 하나입니다. 하지만 직접 VBA 코드를 작성하여 이 작업을 수행할 수 있습니다.

데이터 열에 숫자가 있고 각 숫자의 인접 셀에 해당 행에 어떤 서식을 적용해야 하는지를 나타내는 텍스트가 있다고 가정해 보겠습니다.

다음 코드는 숫자 목록에 대해 실행이 되며 인접한 셀에서 서식 지정 텍스트를 찾은 다음 조건을 충족할 경우 숫자의 서식을 지정합니다:

Sub ReferToAnotherCellForConditionalFormatting()
'행 수를 입력할 변수를 생성합니다.
Dim RRow As Long, N As Long
'행 수를 입력합니다.
RRow = ActiveSheet.UsedRange.Rows.Count
'데이터 범위의 모든 행에 대해서 반복합니다.
For N = 1 To RRow
    'Select Case문을 사용하여 2열을 기준으로 서식을 지정합니다.
    Select Case ActiveSheet.Cells(N, 2).Value
        '셀 색상을 파란색으로 변경합니다.
        Case "Blue"
        ActiveSheet.Cells(N, 1).Interior.Color = vbBlue
        '셀 색상을 빨간색으로 변경합니다.
        Case "Red"
        ActiveSheet.Cells(N, 1).Interior.Color = vbRed
        '셀 색상을 녹색으로 변경합니다.
        Case "Green"
        ActiveSheet.Cells(N, 1).Interior.Color = vbGreen
    End Select
Next N
End Sub

이 코드를 실행하면 위의 원본 워크시트는 아래와 같이 표시됩니다:

서식을 지정하기 위해 참조되는 셀은 워크시트의 아무 곳에나 있을 수 있으며 통합 문서 내의 다른 워크시트에도 있을 수 있습니다. 모든 형태의 텍스트를 사용하여 서식 지정 조건을 만들 수 있으며, 이 코드를 사용할 수 있는 용도는 무한히 많을 수 있습니다.

 

조건부 서식 지정 문에 사용할 수 있는 연산자

이전 예제에서 보았듯이 연산자는 조건 값을 평가하는 방법을 결정하는 데 사용됩니다(예: xlBetween).

규칙 기준을 지정하는 방법에 따라 사용할 수 있는 연산자는 여러 가지가 있습니다.

Name	Value	Description
xlBetween	1	사이 값. 두 개의 수식이 제공된 경우에만 사용할 수 있습니다.
xlEqual	3	같음
xlGreater	5	큼
xlGreaterEqual	7	크거나 같음
xlLess	6	작음
xlLessEqual	8	작거나 같음
xlNotBetween	2	사이값이 아님. 두 개의 수식이 제공된 경우에만 사용할 수 있습니다.
xlNotEqual	4	같지 않음