[Excel] 기준 날짜(I2) + E열 입력으로 G열 날짜 색상 자동 표시하기

이 글에서는 기준 날짜 셀(I2) 과 데이터 입력 열(E열) 을 이용하여, 다른 열(또는 동일한 열)의 날짜들을 자동으로 색상 표시하는 방법을 정리했습니다.

아래에서 소개하는 VBA 코드를 사용하시면, 사용자가 E열에 값을 입력하거나 수정·삭제할 때마다 기준 날짜에 따라 자동으로 색상이 적용되도록 구성하실 수 있습니다.

해당 코드가 반영된 파일은 글의 가장 아래에서 다운로드 하실 수 있습니다.

---

1. 요구사항 정리

구현하고자 하는 요구사항은 다음과 같습니다.

• 기준 날짜는 I2 셀에 입력합니다.
• 사용자가 실제로 편집하는 열은 E열입니다.
• 사용자가 E열에 값을 입력·변경·삭제한 뒤 Enter로 확정할 때마다:
  • 별도의 버튼 클릭 없이 자동으로 매크로가 실행됩니다.
  • I2에 입력된 기준 날짜를 기준으로, 지정한 열(예: E열 또는 G열)의 날짜 셀에 색상이 적용됩니다.
• 색상 규칙은 다음과 같습니다.
  • 기준 날짜 이전인 경우: RGB(255, 255, 0) (노란색)
  • 기준 날짜 이상(같거나 이후) 인 경우: RGB(255, 0, 0) (빨간색)
• 값이 없거나 날짜가 아닌 셀은 항상 색이 없어야 합니다.
• 기준 날짜(I2)가 비어 있거나 날짜 형식이 아닐 경우, 대상 열의 색은 모두 제거되도록 합니다.

아래 예제에서는 E열을 기준으로, E열 자체에 색상을 적용하는 버전을 먼저 설명드리고, 마지막에 G열에 색을 적용하는 변형 방법도 함께 설명드리겠습니다.

---

2. 전체 동작 구조

VBA 구성은 크게 세 부분으로 나눌 수 있습니다.

1. 표준 모듈(Module)
   • 실제 색상 처리 로직을 담고 있는 Sub 프로시저: ColorEColumnByI2
   • 기준 날짜(I2)를 읽어 지정된 범위의 셀들을 검사하고, 조건에 맞게 색을 칠하거나 제거합니다.
2. 시트 모듈(예: Sheet1)
   • Worksheet_Change 이벤트를 사용하여, E열에서 값이 변경될 때마다 ColorEColumnByI2를 호출합니다.
3. (선택 사항) ThisWorkbook 모듈
   • Workbook_BeforeSave 이벤트에서 통합문서를 저장하기 직전에 한 번 더 색상을 정리해 줍니다.

이와 같이 구조를 나누면, 실제 로직은 한 곳(ColorEColumnByI2)에만 존재하고, “언제 실행할지”에 대한 트리거는 이벤트에서만 관리되므로 유지보수가 한결 수월해집니다.

---

3. 색상 처리 로직: 표준 모듈 코드

먼저, Alt + F11로 VBA 편집기를 연 뒤 **[삽입] > [모듈]**을 선택하여 새 모듈을 추가하시고, 아래 코드를 붙여 넣습니다.

Sub ColorEColumnByI2()
    Dim ws As Worksheet
    Dim inputDate As Date
    Dim targetRange As Range
    Dim cell As Range
    Const MAX_ROW As Long = 1000   ' 사용 범위에 맞게 조정 (예: 1000행까지)

    ' 작업 시트 지정 (시트 이름을 실제 사용 중인 이름으로 변경 가능)
    Set ws = ThisWorkbook.Sheets("Sheet1")

    ' E2 ~ E(MAX_ROW) 전체를 항상 검사
    Set targetRange = ws.Range("E2:E" & MAX_ROW)

    ' 기준 날짜(I2)가 유효하지 않으면 -> E열 색만 전부 지우고 종료
    If Not IsDate(ws.Range("I2").Value) Then
        targetRange.Interior.ColorIndex = xlNone
        Exit Sub
    End If

    ' 기준 날짜 설정
    inputDate = CDate(ws.Range("I2").Value)

    ' 각 셀을 기준 날짜와 비교해서 색상 적용
    For Each cell In targetRange
        ' 1) 값이 아예 없으면(빈 셀) 무조건 색 제거
        If Len(Trim(cell.Value)) = 0 Then
            cell.Interior.ColorIndex = xlNone

        ' 2) 날짜로 인식되는 경우만 비교
        ElseIf IsDate(cell.Value) Then
            If CDate(cell.Value) < inputDate Then
                cell.Interior.Color = RGB(255, 255, 0)    ' 기준 이전: 노랑
            Else
                cell.Interior.Color = RGB(255, 0, 0)      ' 기준 이상(같거나 이후): 빨강
            End If

        ' 3) 날짜가 아닌 값(문자, 일반 숫자 등)은 색 제거
        Else
            cell.Interior.ColorIndex = xlNone
        End If
    Next cell
End Sub

코드 설명

• Const MAX_ROW As Long = 1000
  • E열에서 최대 몇 행까지 사용할지 지정합니다.
  • 값이 있다가 삭제된 셀도 이 범위 안에 포함되므로, 삭제 후에도 색이 제대로 제거됩니다.
• If Not IsDate(ws.Range("I2").Value) Then ...
  • 기준 날짜 셀(I2)이 비어 있거나 날짜 형식이 아니면,
    targetRange.Interior.ColorIndex = xlNone로 E열의 색을 전부 지운 후 종료합니다.
• Len(Trim(cell.Value)) = 0
  • 값이 완전히 비어 있는 셀(공백 포함)인지 확인합니다.
  • 이러한 셀은 항상 색이 없어야 하므로, 색을 제거합니다.
• IsDate(cell.Value)
  • 날짜로 인식되는 값만 기준 날짜와 비교합니다.
  • 날짜가 아니면 색을 제거하여, 숫자나 텍스트가 들어 있어도 색이 남지 않도록 합니다.

---

4. E열 변경 시 자동 실행: 시트 모듈 코드

이제 사용자가 E열에 값을 입력·수정·삭제한 뒤 Enter로 확정했을 때, 자동으로 위의 Sub가 실행되도록 이벤트를 연결하겠습니다.

VBA 편집기 왼쪽에서 해당 시트(예: Sheet1) 를 더블 클릭하신 후, 아래 코드를 붙여 넣습니다.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim changedInE As Range

    ' 변경된 셀 중 E열에 해당하는 부분만 추출
    Set changedInE = Intersect(Target, Me.Columns("E"))

    ' E열에서 변경이 없는 경우는 바로 종료
    If changedInE Is Nothing Then Exit Sub

    ' 이벤트 중복 호출 방지
    Application.EnableEvents = False

    ' E열 전체를 기준으로 색상 재적용
    Call ColorEColumnByI2

CleanExit:
    Application.EnableEvents = True
End Sub

동작 방식

• Worksheet_Change 이벤트는 해당 시트에서 셀 값이 실제로 변경되었을 때마다 호출됩니다.
• Intersect(Target, Me.Columns("E"))를 사용하여,
  • 변경된 셀들 중에서 E열에 해당하는 셀만 추출합니다.
  • 만약 E열이 아닌 셀에서 값이 변경된 경우에는 즉시 Exit Sub로 빠져나갑니다.
• E열에서 값이 변경된 것이 확인되면,
  • Application.EnableEvents = False로 이벤트 루프를 잠시 막은 뒤,
  • ColorEColumnByI2를 호출하여 E열 전체에 대해 색상을 다시 계산합니다.
• 마지막에는 항상 Application.EnableEvents = True로 이벤트를 다시 켜 줍니다.

이와 같이 구성하면,

• E열에 값을 새로 입력하셨을 때,
• 기존 값을 다른 값으로 수정하셨을 때,
• 값을 삭제하여 셀이 빈 상태가 되었을 때,

모든 경우에 기준 날짜(I2)를 기준으로 색이 다시 적용되도록 만들 수 있습니다.

---

5. (선택 사항) 저장 시에도 한 번 더 정리하기

추가로, 통합문서를 저장할 때에도 색상을 한 번 더 정리하고 싶으시다면, ThisWorkbook 모듈에 다음 코드를 넣어 주시면 됩니다.

VBA 편집기에서 왼쪽의 ThisWorkbook을 더블 클릭하신 뒤, 아래 코드를 붙여 넣습니다.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Call ColorEColumnByI2
End Sub

이렇게 구성하면,

• 평소에는 E열 편집 시마다 자동으로 색상이 적용되고,
• 통합문서를 저장할 때도 한 번 더 ColorEColumnByI2가 실행되어,
• 중간에 이벤트가 꺼져 있었거나 오류 등으로 인해 반영되지 않은 색이 있을 경우 함께 정리됩니다.

---

6. G열에 색을 적용하고 싶을 때

실제 업무에서는, 입력용 열과 표시용 열을 분리하고 싶은 경우가 자주 있습니다.
예를 들어,

• E열은 입력용 열,
• G열은 날짜 및 색상 표시용 열

으로 사용하고 싶으시다면, 로직은 거의 동일하고 색을 칠하는 대상만 G열로 변경해 주시면 됩니다.

예시로, 기준 날짜는 그대로 I2를 사용하고,

• 비교할 날짜도 G열에 있다고 가정하고,
• 색을 칠하는 것도 G열에 적용하고자 할 때는,

아래와 같이 ColorGColumnByI2라는 별도의 프로시저를 만들어 사용하실 수 있습니다.

Sub ColorGColumnByI2()
    Dim ws As Worksheet
    Dim inputDate As Date
    Dim targetRange As Range
    Dim cell As Range
    Const MAX_ROW As Long = 1000

    Set ws = ThisWorkbook.Sheets("Sheet1")

    ' G열 대상 범위
    Set targetRange = ws.Range("G2:G" & MAX_ROW)

    ' 기준 날짜가 없거나 유효하지 않으면 G열 색 제거 후 종료
    If Not IsDate(ws.Range("I2").Value) Then
        targetRange.Interior.ColorIndex = xlNone
        Exit Sub
    End If

    inputDate = CDate(ws.Range("I2").Value)

    For Each cell In targetRange
        If Len(Trim(cell.Value)) = 0 Then
            cell.Interior.ColorIndex = xlNone
        ElseIf IsDate(cell.Value) Then
            If CDate(cell.Value) < inputDate Then
                cell.Interior.Color = RGB(255, 255, 0)
            Else
                cell.Interior.Color = RGB(255, 0, 0)
            End If
        Else
            cell.Interior.ColorIndex = xlNone
        End If
    Next cell
End Sub

그리고 Worksheet_Change 이벤트에서는,
E열에서 값이 변경될 때마다 ColorGColumnByI2를 호출하도록 변경하시면 됩니다.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim changedInE As Range

    Set changedInE = Intersect(Target, Me.Columns("E"))
    If changedInE Is Nothing Then Exit Sub

    Application.EnableEvents = False
    Call ColorGColumnByI2

CleanExit:
    Application.EnableEvents = True
End Sub

이렇게 하면, E열에 값을 입력·변경·삭제하실 때마다 G열의 날짜가 기준 날짜에 따라 자동으로 색상 표시되도록 동작하게 됩니다.

---

7. 마무리

이 글에서는 다음과 같은 내용을 정리해 보았습니다.

• 기준 날짜 셀(I2)을 기준으로,
• E열에 값이 입력·변경·삭제될 때마다 자동으로 트리거되는 이벤트를 구성하고,
• 지정한 범위(예: 2행 ~ 1000행) 내에서
  • 날짜인 셀은 기준 이전/이상에 따라 서로 다른 색을 적용하고,
  • 빈 셀이나 날짜가 아닌 셀은 색을 제거하는 로직

실제 업무에 적용하실 때에는 다음 요소들만 상황에 맞게 조정하시면 됩니다.

• 기준 날짜 셀 주소(I2)
• 입력 열(E열)
• 색을 적용할 열(E열 또는 G열)
• 최대 행 수(MAX_ROW)

이 구조의 장점은 다음과 같습니다.

• 실제 로직은 하나의 Sub에만 존재하기 때문에 관리가 쉽고,
• 이벤트 프로시저는 “언제 실행할지”에 대한 트리거 역할만 담당하여,
• 나중에 다른 시트나 다른 통합문서에 가져와 재사용하기에도 용이합니다.

업무 환경에 맞추어 ColorEColumnByI2 또는 ColorGColumnByI2 부분만 적절히 수정하셔서, 각 시트별로 서로 다른 색상 규칙을 적용하시는 것도 가능합니다.

 

적용 엑셀 파일 다운로드

E열 날짜 기반 구성.xlsm

0.03MB