(VBA)엑셀 조건부 서식 생성 VBA 프로그램 만들기

엑셀 조건부 서식을 이용하는 방법을 (참고 : 엑셀에서 "조건부 서식"을 얼마나 활용하시나요? (tistory.com)) 학습하셨다면 오늘은 VBA 프로그램을 통해 조건부 서식을 초기화하고 생성하는 VBA 프로그램 만들어 보겠습니다. 

그런데, "엑셀 홈 메뉴에서 "조건부 서식" 툴바를 사용하여 조건부 서식을 지정하는 것으로도 충분할 것 같은데, 왜 VBA 프로그램까지 필요한 걸까요?" 이런 궁금증이 들지 않으신가요? 단순한 호기심이나 블로크 컨텐츠가 필요해서 만들게 된 것은 아닙니다. 많은 양의 복잡한 표 데이터를 관리하다 보면 데이터 행을 추가하고 삭제하는 많은 반복 작업도 필요하게 됩니다. 이런 과정에 반복되면 (그림2)와 같이 초기 설정된 조건부 서식은 적용 범위에 따라 여러개로 쪼개지고 심지어 복사된 표 데이터를 추가하는 경우 조건부 서식이 적용되지 않는 상황도 발생하게됩니다. 그래서 (그림3)과 같이 조건부 서식이 복잡해 지면 조건부 서식을 다시 설정하는 반복 작업이 필요하게 됩니다. 그래서 반복 작업을 싫어하는 사람이 조건부 서식 초기화, 재 설정 VBA 프로그램을 개발하게 되었지요. 아래 예제는 단순하고 적은양의 표 데이터이지만 조건부 서식을 자동 재설정하는 방법과 개념을 동일합니다. 

 

1. "(VBA)조건부 서식 자동 생성하기" 엑셀 소개

(그림1)은 조건부 서식 규칙을 VBA프로그램을 통해 자동 생성하고 초기화 하는 엑셀 VBA 샘플 입니다.

규칙1,2,3번 3가지 조건부 서식을 조건부 서식 초기화 버튼을 클릭하면 "조건부 서식 적용 대상 표 데이터 샘플"에 적용합니다. 이때 규칙 지우기 범위에 따라 "시트 전체" 또는 "적용 범위"의 조건부 서식 규칙을 삭제하고 생성하게 됩니다.

적용 대상 범위의 마지막 행은 표 데이터에 따라 계속 변화되기 때문에 VBA 프로그램에서 마지막 행을 자동으로 찾아 범위가 설정되도록 합니다.

(그림1) VBA프로그램을 활용한 조건부 서식 초기화 샘플

 

(그림2) 규칙과 적용대상이 잘 정리되어 있는 초기 조건부 서식

(그림3) 표 데이터 작업으로 어지러워진 조건부 서식 규칙들...

 

2. VBA 프로그램 코드 이해하기

그럼 예제 프로그램을 보면서 조건부 서식 초기화, 재 설정 방법을 알아 보도록 하겠습니다.

"조건부 서식 초기화" 버튼 클릭시 호출 되는 Sub 함수

Sub ApplyAllConditionalFormatting()     Dim ws As Worksheet     Dim rng1 As Range, rng2 As Range     Dim cf1 As FormatCondition, cf2 As FormatCondition, cf3 As FormatCondition     Dim lastRow As Integer          ' 현재 워크시트 설정     Set ws = ThisWorkbook.Sheets("Sheet1")          ' 표 데이터의 마지막 Row No 찾기     lastRow = ws.Cells(ws.Rows.Count, "D").End(xlUp).Row ' 표 데이터 "항목" 컬럼의 데이터가 있는 마지막 Row no 찾아오기          ' 첫 번째 조건부 서식을 적용할 범위 설정     Set rng1 = ws.Range(ws.Range("규칙1적용대상컬럼").Value & ws.Range("규칙1적용대상행").Value & ":" & ws.Range("규칙1적용대상컬럼").Value & lastRow)          ' 두 번째 조건부 서식을 적용할 범위 설정     Set rng2 = ws.Range(ws.Range("규칙2적용대상컬럼").Value & ws.Range("규칙2적용대상행").Value & ":" & ws.Range("규칙2적용대상컬럼").Value & lastRow)          ' 세 번째 조건부 서식을 적용할 범위 설정     Set rng3 = ws.Range(ws.Range("규칙3적용대상컬럼").Value & ws.Range("규칙3적용대상행").Value & ":" & ws.Range("규칙3적용대상컬럼End").Value & lastRow)          ' 기존 조건부 서식 삭제     If ws.Range("규칙지우기범위").Value = "적용 범위" Then         rng1.FormatConditions.Delete         rng2.FormatConditions.Delete         rng3.FormatConditions.Delete     ElseIf ws.Range("규칙지우기범위").Value = "시트 전체" Then         ws.Cells.FormatConditions.Delete     Else         MsgBox "규칙 지우기 방법을 선택하고 다시 시도하세요."         Exit Sub     End If     ' 첫 번째 조건: 셀 값이 50보다 큰 경우 빨간색 배경     Set cf1 = rng1.FormatConditions.Add(Type:=xlCellValue, Operator:=xlGreater, Formula1:="30%")     With cf1         .Font.Color = ws.Range("규칙1적용서식").Font.Color         .Font.Bold = ws.Range("규칙1적용서식").Font.Bold     End With     ' 두 번째 조건: 셀 값이 50 이하인 경우 녹색 배경     Set cf2 = rng2.FormatConditions.Add(Type:=xlCellValue, Operator:=xlLessEqual, Formula1:="5%")     With cf2         .Font.Color = ws.Range("규칙2적용서식").Font.Color         .Font.Bold = ws.Range("규칙2적용서식").Font.Bold     End With     ' 조건부 서식 추가: F 셀의 값이 국내산이 아닌 경우     Set cf3 = rng3.FormatConditions.Add(Type:=xlExpression, Formula1:="=$D" & ws.Range("규칙3적용대상행").Value & "<>""국내산""")     With cf3         .Interior.Color = ws.Range("규칙3적용서식").Interior.Color         .SetLastPriority     End With End Sub

 

1) 표 데이터의 마지막 Row 번호 찾기

표 데이터의 "D"컬럼을 기준으로 표의 마지막 Row 번호를 찾는 방법으로 마지막 Row에서 위로 이동 데이터가 있는 마지막 Row번호를 찾는 코드 입니다. 이때 ws.Rows.Count를 사용하는 이유는 엑셀 버전에 따라 Sheet의 마지막 Row번호가 다르기 때문이며, End(xlUp).Row는 아래서 위로 데이터 Row를 찾는 명령 입니다.

' 표 데이터의 마지막 Row No 찾기     lastRow = ws.Cells(ws.Rows.Count, "D").End(xlUp).Row ' 표 데이터 "항목" 컬럼의 데이터가 있는 마지막 Row no 찾아오기

 

2) 조건부 서식 적용 대상 범위 설정하기

Sheet에 셀 값을 가져오는 방법으로 엑셀의 "이름 관리자"를 사용했으며, ws.Range("규칙1적용대상컬럼").Value와 같이 Cell의 컬럼, Row 번호 대산 이름을 사용하여 규칙 적용 범위정보를 가져오도록 처리한 코드 입니다.

' 첫 번째 조건부 서식을 적용할 범위 설정     Set rng1 = ws.Range(ws.Range("규칙1적용대상컬럼").Value & ws.Range("규칙1적용대상행").Value & ":" & ws.Range("규칙1적용대상컬럼").Value & lastRow)          ' 두 번째 조건부 서식을 적용할 범위 설정     Set rng2 = ws.Range(ws.Range("규칙2적용대상컬럼").Value & ws.Range("규칙2적용대상행").Value & ":" & ws.Range("규칙2적용대상컬럼").Value & lastRow)          ' 세 번째 조건부 서식을 적용할 범위 설정     Set rng3 = ws.Range(ws.Range("규칙3적용대상컬럼").Value & ws.Range("규칙3적용대상행").Value & ":" & ws.Range("규칙3적용대상컬럼End").Value & lastRow)

 

3) 기존 조건부 서식 삭제 하기

아래 코드는 초기화를 위해 기존 조건부 서식을 삭제하는 부분으로 "규칙지우기범위" 설정 정보에 따라 지정된 Range만 삭제 하거나 아니면 Sheet 전체에서 조건부 서식을 삭제하도록 처리한 코드 입니다. 

' 기존 조건부 서식 삭제     If ws.Range("규칙지우기범위").Value = "적용 범위" Then         rng1.FormatConditions.Delete         rng2.FormatConditions.Delete         rng3.FormatConditions.Delete     ElseIf ws.Range("규칙지우기범위").Value = "시트 전체" Then         ws.Cells.FormatConditions.Delete     Else         MsgBox "규칙 지우기 방법을 선택하고 다시 시도하세요."         Exit Sub     End If

 

 

4) 셀 Value 조건의 조건부 서식 생성 및 서식 설정하기

아래 코드는 조건부 서식의 규칙 유형이 셀 Value조건 인 "규칙1"에 대한 조건부 서식을 생성하고 생성된 조건부 서식에 서식을 적용하는 코드 입니다. 이때 "규칙1적용서식" 이름으로 지정된 셀의 Font 컬러, Bold 설정을 그대로 조건부 서식으로 적용합니다. 

' 첫 번째 조건: 셀 값이 50보다 큰 경우 빨간색 배경     Set cf1 = rng1.FormatConditions.Add(Type:=xlCellValue, Operator:=xlGreater, Formula1:="30%")     With cf1         .Font.Color = ws.Range("규칙1적용서식").Font.Color         .Font.Bold = ws.Range("규칙1적용서식").Font.Bold     End With

 

5) Expression 조건의 조건부 서식 생성 및 서실 설정하기

Formula1에 "D" 커럼의 값이 국내산이 아닌 경우 참을 리턴하도록 수식을 적용 합니다. 이때 완성된 규칙의 수식은 =$D15<>"국내산" 형태가 되며, 수식에서 $D15의 의미는 "D"컬럼은 고정하고 행은 15행을 기준으로 상대 참조하는 것을 의미 합니다. 즉 매 행의 "D"컬럼 값을 읽어 "국내산"이 아니면 수식은 참을 리턴하며, 참인 경유 "규칙3적용서식"에 지정된 Interior 컬러(백그라운드 컬러)가 셀에 적용됩니다.

' 조건부 서식 추가: F 셀의 값이 국내산이 아닌 경우     Set cf3 = rng3.FormatConditions.Add(Type:=xlExpression, Formula1:="=$D" & ws.Range("규칙3적용대상행").Value & "<>""국내산""")     With cf3         .Interior.Color = ws.Range("규칙3적용서식").Interior.Color         .SetLastPriority     End With

 

지금까지 설명드린 VBA 프로그램이 포함된 엑셀 파일을 첨부 합니다. 엑셀 파일을 받아 내려 직접 테스트해 보시고 이후 활용해 보시기 바랍니다. 감사합니다.

(VBA)조건부 서식 자동 생성하기.xlsm

0.02MB

엑셀에서 "조건부 서식"을 얼마나 활용하시나요?