(VBA)엑셀 조건부 서식이 적용된 Cell의 평가 값이 참(True)인 Cell를 찾기

엑셀 VBA 프로그램을 사용하여 "수식을 사용하여 서식을 지정할 셀 결정" 조건부 서식이 적용된 Cell의 수식 평가 값이 참인 Cell을 찾는 방법으로 Application.ConvertFormula 메서드 사용법과 활용 예제 알아보겠습니다.

그런데, 그냥 엑셀 Sheet를 보면 조건부 서식이 참인 셀을 확인 할 수 있는데 왜 이런 게 필요했을까요? 

조건부 서식이 적용된 Sheet를 공유 목적으로 변환 할 때 경우에 따라 조건부 서식이 없는 엑셀 파일로 공유해야 하는 경우가 발생할 수 있습니다. 즉 공유할 엑셀 파일에서 조건부 서식은 삭제하고 조건부 서식 평가 값이 참인 셀의 Font, Color를 별도 지정하여 엑셀 파일을 공유하고자 하는 거죠.

그럼 우선 Application.ConvertFormula 메서드 사용법에 대해 먼저 알아보고, "수식을 사용하여 서식을 지정할 셀 결정" 조건부 서식에 셀 수식 평가 값이 참이 Cell을 찾아 Font, Color를 변경하는 방법을 알아 보겠습니다.

1. Application.ConvertFormula 메서드 사용법 

Application.ConvertFormula 메서드는 엑셀 VBA에서 수식을 다른 참조 스타일이나 절대/상대 참조 형식으로 변환하는 데 사용됩니다. 이 메서드는 특히 조건부 서식 수식을 다른 셀 참조 형식으로 변환해야 할 때 유용합니다.

1) ConvertFormula 메서드의 구문

Application.ConvertFormula(Formula, FromReferenceStyle, ToReferenceStyle, ToAbsolute, RelativeTo)

인수 설명

1. Formula (필수): 변환할 수식입니다. 문자열 형식이어야 합니다.
2. FromReferenceStyle (선택): 원래 수식의 참조 스타일입니다. 생략하면 xlA1으로 간주합니다. 가능 값:
   • xlA1: A1 참조 스타일
   • xlR1C1: R1C1 참조 스타일
3. ToReferenceStyle (선택): 변환할 참조 스타일입니다. 생략하면 xlA1으로 간주합니다. 가능 값:
   • xlA1: A1 참조 스타일
   • xlR1C1: R1C1 참조 스타일
4. ToAbsolute (선택): 참조를 절대 참조로 변환하는 방법을 지정합니다. 가능 값:
   • xlAbsolute: 절대 참조 (예: $A$1)
   • xlRelative: 상대 참조 (예: A1)
   • xlAbsRowRelColumn: 절대 행, 상대 열 (예: $A1)
   • xlRelRowAbsColumn: 상대 행, 절대 열 (예: A$1)
5. RelativeTo (선택): 수식에서 상대 참조를 계산할 기준 셀입니다. 생략하면 현재 활성 셀을 기준으로 합니다.

2) 예제

예제 1: A1 참조 스타일을 R1C1 참조 스타일로 변환

Sub ConvertA1toR1C1()     Dim originalFormula As String     Dim convertedFormula As String          originalFormula = "A1"     convertedFormula = Application.ConvertFormula(Formula:=originalFormula, FromReferenceStyle:=xlA1, ToReferenceStyle:=xlR1C1)          MsgBox "Converted Formula: " & convertedFormula End Sub

이 코드는 "A1" 참조 스타일을 R1C1 참조 스타일로 변환합니다.

예제 2: 수식을 절대 참조로 변환

Sub ConvertToAbsolute()     Dim originalFormula As String     Dim convertedFormula As String          originalFormula = "A1"     convertedFormula = Application.ConvertFormula(Formula:=originalFormula, FromReferenceStyle:=xlA1, ToAbsolute:=xlAbsolute)          MsgBox "Converted Formula: " & convertedFormula End Sub

이 코드는 "A1" 수식을 절대 참조로 변환하여 "$A$1"을 반환합니다.

2. 조건부 서식이 적용된 셀 수식 평가 

"수식을 사용하여 서식을 지정할 셀 결정"  조건부 서식의 수식을 평가할 때 ConvertFormula를 사용하는 방법을 알아 보겠습니다.  아래 (그림1)의 수식은 적용 대상 셀을 상대참조하는 수식 입니다. 이 적용 대상 셀 별로 수식을 평가하기 위해서는  수식 "=D2<10%"에 "D2"를 상대 참조 셀로 변경해야 하는데 이때 ConvertFormula를 사용하는 방법 입니다.

(그림1) " 수식을 사용하여 서식을 지정할 셀 결정 " 조건부 서식

 

(그림2)는 조건부 서식이 적용된 마진율 컬럼("D")에서 수식이 참인 경우 노락색 바당색에 파란색 폰트로 시각화 되어 있는 예 입니다. "수식 참인 셀 찾기" 버튼을 클릭하여 조건부 서식이 참인 셀을 찾아 "A2" 셀의 Font와 Color로 변경하는 VBA 프로그램 예제를 보겠습니다.

(그림2) 조건부 서식이 적용된 셀(마진율 컬럼)을 찾아 "A2"컬럼 Font, Color로 변경하기 예

 

아래 VBA 예제 프로그램은 1) 조건부 서식을 찾고자 하는 셀 범위 설정하고 2)모든 셀을 순회하면서 조건부 서식을 확인, 3) 결과 출력하는 아주 간단한 로직 입니다. 여기서 2)모든 셀을 순회하면서 조건부 서식을 확인하는 부분을 자세하게 살펴 보겠습니다.

우선 For 루프 문이 2번 사용되는 데 첫번째 For 루프는 검색하고자 하는 셀 범위(즉 rng) 전체를 탐색하는 루프 문 입니다. 두번째 For루프는 각 셀에 적용된 조건부 서식(cell.FormatConditions)에 대해서 루프를 돌며 조건부 서식의 수식을 평가합니다.

이때 "If cond.Type = xlExpression Then"는 조건부 서식의 유형이 "수식을 사용하여 서식을 지정할 셀 결정 " 인지 확인하는 If 문이며, if 문이 참이면 2-1) 수식에서 상대 참조 셀를 변환하여 변환된 수식을 평가하고 조건부 서식의 2-2) 수식이 참이면 셀의 Font와 Color를 "A2" 셀에 지정된 Font와 Color로 변경 합니다. 

 

여기서 주의해야 할 점은 수식의 상대 참조 셀을 계산하는 방법 입니다.

아래와 같이 Application.ConvertFormula메서드를 2번 호출한 이유는 탐색하는 cell를 기준으로 수식을 변환하기 위해서는 조건부 서식의 수식을 xlR1C1 형태로 변환해야 Cell에 대한 상태 참조 수식 변환이 정상적으로 가능하기 때문 입니다. 제가 여러번 테스트해 본 결과로는 xlA1  형식으로 작성된 수식을 ConvertFormula를 통해 상대 참조로 변환되지 않는 문제가 있었고 이문제의 원인은 정확히 알수는 없었습니다. (테스트 환경 : office 365)

xyz = Application.ConvertFormula(cond.Formula1, xlA1, xlR1C1, xlRelative, cond.AppliesTo.Cells)                 abc = Application.ConvertFormula(xyz, xlR1C1, xlA1, xlRelative, cell)

 

아래 예제 프로그램 코드와 엑셀 파일을 첨부 합니다. 필요하신 분은 다운로드 받아서 테스트해보시고 유용하게 사용하 실 수 있었으면 합니다. 감사합니다.

• 예제 VBA 프로그램 코드

Sub FindCellsWithTrueCondition()     Dim ws As Worksheet     Dim rng As Range     Dim cell As Range     Dim cond As FormatCondition     Dim foundCells As Range     Dim cellFound As Boolean          ' 워크시트 설정     Set ws = ThisWorkbook.Sheets("Sheet3")          ' 1) 조건부 서식을 찾고자 하는 셀 범위 설정     Set rng = ws.Range("B2:E10")          ' 조건부 서식이 참인 셀들을 저장할 Range 변수 초기화     Set foundCells = Nothing     ' 2)모든 셀을 순회하면서 조건부 서식을 확인     For Each cell In rng         cellFound = False         For Each cond In cell.FormatConditions             If cond.Type = xlExpression Then                 '// 2-1) 수식에서 상대 참조 셀 계산 --> 상대 참조 셀 수식 평가                 xyz = Application.ConvertFormula(cond.Formula1, xlA1, xlR1C1, xlRelative, cond.AppliesTo.Cells)                 abc = Application.ConvertFormula(xyz, xlR1C1, xlA1, xlRelative, cell)                 If Evaluate(abc) Then                     Debug.Print xyz; " --> " & abc & " ( " & cell.Row; ", " & cell.Column & ")"                                          '// 2-2) 수식 평가 값이 참이 셀의 Font, Color 변경                     cell.Font.Color = ws.Cells(2, "A").Font.Color                     cell.Font.Bold = ws.Cells(2, "A").Font.Bold                     cell.Interior.Color = ws.Cells(2, "A").Interior.Color                                          cellFound = True                     Exit For                 End If             End If         Next cond                  If cellFound Then             If foundCells Is Nothing Then                 Set foundCells = cell             Else                 Set foundCells = Union(foundCells, cell)             End If         End If     Next cell          ' 3) 결과 출력     If Not foundCells Is Nothing Then         MsgBox "조건부 서식이 참인 셀: " & foundCells.Address     Else         MsgBox "조건부 서식이 참인 셀을 찾을 수 없습니다."     End If End Sub

 

• 예제 프로그램 테스트 가능 엑셀 파일

VBA 조건부 서식 참값 셀 찾기.xlsm

0.02MB