새로운 코드

[김진태]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Set ws = Me

' 감시할 영역: K열(행별 완료) 또는 O열(개별 완료 리스트)
If Intersect(Target, ws.Range("K:K")) Is Nothing And _
Intersect(Target, ws.Range("O:O")) Is Nothing Then Exit Sub

Application.EnableEvents = False
On Error GoTo CleanExit

Dim lastRow As Long
lastRow = ws.Cells(ws.Rows.Count, "E").End(xlUp).Row
If lastRow < 2 Then GoTo CleanExit

' 1) O열 전체에서 완료된 개별 코드들 수집
Dim completed As Object
Set completed = CreateObject("Scripting.Dictionary") ' 키셋 사용(대소문자 구분 비활성)
Dim r As Long
Dim raw As String, parts As Variant, p As Variant
For r = 2 To ws.Cells(ws.Rows.Count, "O").End(xlUp).Row
raw = Trim(ws.Cells(r, "O").Value)
If Len(raw) > 0 Then
' 구분자: 쉼표, 슬래시, 공백 허용
raw = Replace(raw, "/", ",")
raw = Replace(raw, " ", ",")
parts = Split(raw, ",")
For Each p In parts
p = Trim(p)
If Len(p) > 0 Then
If Not completed.Exists(UCase(p)) Then completed.Add UCase(p), True
End If
Next p
End If
Next r

' 2) 각 행의 E열 텍스트를 검사해서 취소선 적용(또는 K열 행완료 체크)
Dim epsText As String
Dim strike As Boolean
Dim key As Variant
For r = 2 To lastRow
strike = False
' 행완료(K열) 체크: O 또는 0 입력 시
If Len(Trim(ws.Cells(r, "K").Value)) > 0 Then
Select Case LCase(Trim(ws.Cells(r, "K").Value))
Case "o", "0"
strike = True
End Select
End If

' 개별완료 코드와 매칭 검사
If Not strike Then
epsText = CStr(ws.Cells(r, "E").Value)
If Len(Trim(epsText)) > 0 And completed.Count > 0 Then
' 대소문자 구분 없이 포함 여부 검사
For Each key In completed.Keys
If InStr(1, epsText, CStr(key), vbTextCompare) > 0 Then
strike = True
Exit For
End If
Next key
End If
End If

ws.Cells(r, "E").Font.Strikethrough = strike
Next r

CleanExit:
Application.EnableEvents = True
End Sub