반복되는 귀찮은 일, 엑셀에게 시키기-조건부 서식 적용

안녕하세요.

 

메일로 질문을 받았는데, 결론은 수작업을 할 수 밖에 없는 작업이었습니다.

 

45개의 셀에 4개 이상의 조건부 서식을 적용해야 하는 일입니다.

수작업이라도 마음 먹고 하면 10 여 분쯤이면 할 수 있을 것 같은데, 이런 작업을 엑셀에게 시키는 법을 배워두면 다음에 비슷한 작업이 있을 때 쉽게 처리할 수 있습니다.

그래서 소개합니다.

 

실습 파일 다운로드

조건부서식 적용_실습.xlsx

0.01MB

 

완성 파일 다운로드

조건부서식 적용_완성.xlsm

0.02MB

 

[C2] 셀부터 [E8] 셀까지 1부터 45까지 숫자가 입력되어 있습니다.

[L2:L46] 셀 범위의 숫자에 따라 채우기 색이 바뀌도록 조건부 서식을 적용하려 합니다.

L열 값이 0이면 빨강, 1이면 흰색, 2면 파랑, 3이면 녹색을 나타냅니다.

 

매크로를 하나 만듭니다.

 

[C2] 셀에 커서를 두고 [L2] 셀 값이 0이면 빨강으로 나타나도록 [조건부 서식]을 작성합니다.

 

[보기]-[매크로]-[매크로 기록]을 선택합니다.

[홈]-[스타일]-[조건부 서식]-[새 규칙]을 선택합니다.

'수식'을 선택해서 [L2] 셀 값이 0인 경우에 빨강으로 지정하는 조건부 서식을 작성합니다.

[보기]-[매크로]-[기록 중지]를 선택합니다.

 

[보기]-[매크로]-[매크로 보기]를 선택합니다.

'매크로1'을 선택한 뒤 [편집]을 클릭합니다.

VBE가 실행되고 만들어진 매크로 코드가 보입니다.

Sub 매크로1()
'
' 매크로1 매크로
'

'
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$L$2=0"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
End Sub

코드를 모두 알 필요없고, 알 수도 없습니다.

눈치껏 몇가지만 알아서 적당히 쓰면 됩니다.

 

Selection은 조건부 서식이 적용될 [C2] 셀입니다.

Formula1 부분에 조건식을 적는데, 현재는 0인 경우에 대한 조건입니다.

.Color 은 채우기 색을 의미합니다.

 

Color 대신 ColorIndex를 쓰려고 합니다.

정해진 색상을 쓸 땐 간단한 숫자로 지정할 수 있습니다.

빨강은 ColorIndex 3입니다.

 

2021.08.11 - 엑셀 팁] 사용자 지정 서식에서 사용할 수 있는 색상

엑셀 팁] 사용자 지정 서식에서 사용할 수 있는 색상

 

[L2] 셀 값이 1인 경우 조건부 서식을 만들 땐 현재 코드를 복사해서 고칠 부분을 고치면 됩니다.

Sub 매크로1()
'
' 매크로1 매크로
'

'
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$L$2=0"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ColorIndex = 3
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$L$2=1"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ColorIndex = 5
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
End Sub

 

[C2] 셀에만 조건부 서식을 만드는 것이 아니라 45개 셀 모두에 같은 코드를 만들어야 합니다.

 

반복문을 이용합니다.

 

[C2:I8] 셀 범위를 지정해서 반복하려면 For Each 명령문이 적당합니다.

For Each C in Selection

Selection은 범위 선택한 셀을 의미합니다.

[C2:I8] 셀 범위를 선택할 예정이니 Selection은 여러 개 셀입니다.

그 중 셀 하나를 변수 C에 할당합니다.

이렇게 하나씩 Selection에 포함된 모든 셀을 C에 할당합니다.

 

원래 코드 중 Selection은 C로 모두 바꿉니다.

Sub 매크로1()
'
' 매크로1 매크로
'

'
    For Each C In Selection
        C.FormatConditions.Add Type:=xlExpression, Formula1:="=$L$2=0"
        C.FormatConditions(C.FormatConditions.Count).SetFirstPriority
        With C.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .ColorIndex = 3
            .TintAndShade = 0
        End With
        C.FormatConditions(1).StopIfTrue = False
        
        C.FormatConditions.Add Type:=xlExpression, Formula1:="=$L$2=1"
        C.FormatConditions(C.FormatConditions.Count).SetFirstPriority
        With C.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .ColorIndex = 5
            .TintAndShade = 0
        End With
        C.FormatConditions(1).StopIfTrue = False
    Next C
End Sub

[C2:I8] 셀 범위 중 [F8:I8] 셀 범위는 값이 없으므로 반복문이 실행될 필요가 없습니다.

If문을 써서 실행되지 않도록 처리합니다.

Sub 매크로1()
'
' 매크로1 매크로
'

'
    For Each C In Selection
        If IsEmpty(C.Value) Then
            Exit For
        End If
        
        C.FormatConditions.Add Type:=xlExpression, Formula1:="=$L$2=0"
        C.FormatConditions(C.FormatConditions.Count).SetFirstPriority
        With C.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .ColorIndex = 3
            .TintAndShade = 0
        End With
        C.FormatConditions(1).StopIfTrue = False
        
        C.FormatConditions.Add Type:=xlExpression, Formula1:="=$L$2=1"
        C.FormatConditions(C.FormatConditions.Count).SetFirstPriority
        With C.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .ColorIndex = 5
            .TintAndShade = 0
        End With
        C.FormatConditions(1).StopIfTrue = False
    Next C
End Sub

[C2] 셀은 [L2] 셀 값에 따라 서식이 적용됩니다.

[D2] 셀은 [L3] 셀 값에 따라 서식이 적용됩니다.

...

[C3] 셀은 [L8] 셀 값에 따라 서식이 적용됩니다.

 

Formula1 조건 수식의 셀 주소를 바뀌도록 작성해야 합니다.

 

[C2] 셀 값인 1인 경우 [L2] 셀

[D2] 셀 값인 2인 경우 [L3] 셀

...

[C3] 셀 값인 8인 경우 [L9] 셀

 

규칙은 셀 값+1한 L열 주소입니다.

 

이렇게 규칙을 찾는 것이 처음에는 생소하지만 몇 번 해 보면 이 정도 쉬운 것은 저절로 보이게 됩니다.

 

Formula1 부분을 고칩니다.

Formula1:="=$L$" & C.Value + 1 & "=0"

 

코드를 모두 수정합니다.

Sub 매크로1()
'
' 매크로1 매크로
'

'
    For Each C In Selection
        If IsEmpty(C.Value) Then
            Exit For
        End If
        
        C.FormatConditions.Add Type:=xlExpression, Formula1:="=$L$" & C.Value + 1 & "=0"
        C.FormatConditions(C.FormatConditions.Count).SetFirstPriority
        With C.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .ColorIndex = 3
            .TintAndShade = 0
        End With
        C.FormatConditions(1).StopIfTrue = False
        
        C.FormatConditions.Add Type:=xlExpression, Formula1:="=$L$" & C.Value + 1 & "=1"
        C.FormatConditions(C.FormatConditions.Count).SetFirstPriority
        With C.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .ColorIndex = 5
            .TintAndShade = 0
        End With
        C.FormatConditions(1).StopIfTrue = False
    Next C
End Sub

 

코드가 아직 다 완성된 것은 아니지만 어느 정도 실행시킬 정도가 되었기 때문에 테스트를 해 봅니다.

그 전에 꼭 저장을 해야 합니다.

제대로 동작하겠지만, 혹시 문제가 생긴다면 저장하지 않은 모든 내용을 잃을 수 있습니다.

그래서 꼭 실행하기 전에 저장을 해야 합니다.

 

저장할 때 매크로가 포함되었기 때문에 파일 형식은 Excel 매크로 사용 통합 문서(*.xlsm) 이어야 합니다.

 

파일을 저장했으면 엑셀 창에서 [C2:I8] 셀 범위를 선택한 뒤 매크로를 실행합니다.

 

[C2:I8] 셀 범위를 선택하고 [보기]-[매크로]-[매크로 보기]를 선택합니다.

'매크로1'이 선택된 상태에서 [실행]을 클릭합니다.

제대로 동작했습니다.

 

L열 값이 2와 3인 경우도 처리되도록 코드를 수정하고 매크로 이름도 적당이 바꿉니다.

시트 전체의 설정된 조건부 서식을 지우는 매크로도 하나 만듭니다.

 

Sub 조건부서식적용()
    For Each C In Selection
        If IsEmpty(C.Value) Then
            Exit For
        End If
        
        C.FormatConditions.Add Type:=xlExpression, Formula1:="=$L$" & C.Value + 1 & "=0"
        C.FormatConditions(C.FormatConditions.Count).SetFirstPriority
        With C.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .ColorIndex = 3
            .TintAndShade = 0
        End With
        C.FormatConditions(1).StopIfTrue = False
        
        C.FormatConditions.Add Type:=xlExpression, Formula1:="=$L$" & C.Value + 1 & "=1"
        C.FormatConditions(C.FormatConditions.Count).SetFirstPriority
        With C.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .ColorIndex = 2
            .TintAndShade = 0
        End With
        C.FormatConditions(1).StopIfTrue = False
        
        C.FormatConditions.Add Type:=xlExpression, Formula1:="=$L$" & C.Value + 1 & "=2"
        C.FormatConditions(C.FormatConditions.Count).SetFirstPriority
        With C.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .ColorIndex = 5
            .TintAndShade = 0
        End With
        C.FormatConditions(1).StopIfTrue = False
        
        C.FormatConditions.Add Type:=xlExpression, Formula1:="=$L$" & C.Value + 1 & "=3"
        C.FormatConditions(C.FormatConditions.Count).SetFirstPriority
        With C.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .ColorIndex = 10
            .TintAndShade = 0
        End With
        C.FormatConditions(1).StopIfTrue = False
    Next C
End Sub

Sub 조건부서식지우기()
    Cells.FormatConditions.Delete
End Sub

 

 

10 여 분 수작업을 하면 될 작업이지만, 앞으로 이런 귀찮은 작업은 가능한 한 엑셀이 알아서 하도록 명령을 내리는 방법인 VBA를 배웁시다.

VBA를 모두 다 배우려면 한참 걸리지만, 필요한 부분만, 눈치껏, 검색도 하면서 쓴다면 당장부터도 쓸 수 있습니다.

 

필요하신 분께 도움이 되길 바랍니다.