Excel에서 특정 문자 찾기 VBA사용

Sub ChangeTextColorOptimized()
    Dim ws As Worksheet
    Dim cell As Range
    Dim searchText As String
    Dim startPos As Long
    Dim currentPos As Long
    Dim lastRow As Long
    Dim lastCol As Long

    ' 현재 활성화된 워크시트 설정
    Set ws = ActiveSheet
    
    ' 찾을 텍스트 지정
    searchText = "from" ' 원하는 텍스트로 변경

    ' 데이터가 있는 범위만 처리하도록 최적화
    With ws
        lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        lastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
    End With

    ' 데이터가 있는 범위만 순회
    For Each cell In ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol))
        If Not IsEmpty(cell.Value) And VarType(cell.Value) = vbString Then
            currentPos = 1 ' 셀 내 검색 시작 위치 초기화
            Do
                ' 텍스트 위치 찾기
                startPos = InStr(currentPos, cell.Value, searchText, vbTextCompare)
                If startPos > 0 Then
                    ' 특정 텍스트 색 변경
                    With cell.Characters(Start:=startPos, Length:=Len(searchText)).Font
                        .Color = RGB(255, 0, 0) ' 빨간색으로 설정
                    End With
                    ' 다음 검색 시작 위치로 이동
                    currentPos = startPos + Len(searchText)
                Else
                    Exit Do ' 더 이상 텍스트가 없으면 반복 종료
                End If
            Loop
        End If
    Next cell
End Sub

Sub FormatWhereAndConditionsWithoutRegex()
    Dim ws As Worksheet
    Dim cell As Range
    Dim sqlText As String
    Dim posWhere As Long
    Dim posAnd As Long
    Dim nextPos As Long
    Dim columnName As String
    Dim startPos As Long
    Dim words() As String
    Dim i As Long

    ' 현재 워크시트
    Set ws = ActiveSheet

    ' L열의 사용된 범위 순회
    For Each cell In ws.Range("L1:L" & ws.Cells(ws.Rows.Count, "L").End(xlUp).Row)
        If Not IsEmpty(cell.Value) Then
            sqlText = cell.Value
            
            ' "WHERE" 조건 처리
            posWhere = InStr(1, sqlText, "where ", vbTextCompare)
            If posWhere > 0 Then
                nextPos = posWhere + 6
                words = Split(Mid(sqlText, nextPos), " ")
                For i = LBound(words) To UBound(words)
                    If LCase(words(i)) = "and" Or LCase(words(i)) = "order" Then Exit For
                    If InStr(words(i), "=") > 0 Or InStr(words(i), ":") > 0 Or InStr(words(i), "(") > 0 Then
                        columnName = Trim(Split(words(i), "=")(0))
                        columnName = Split(columnName, "(")(UBound(Split(columnName, "(")))
                        startPos = InStr(1, sqlText, columnName, vbTextCompare)
                        If startPos > 0 Then
                            With cell.Characters(Start:=startPos, Length:=Len(columnName)).Font
                                .Size = 16
                                .Bold = True
                            End With
                        End If
                    End If
                Next i
            End If

            ' "AND" 조건 처리
            posAnd = 1
            Do
                posAnd = InStr(posAnd, sqlText, "and ", vbTextCompare)
                If posAnd > 0 Then
                    nextPos = posAnd + 4
                    words = Split(Mid(sqlText, nextPos), " ")
                    For i = LBound(words) To UBound(words)
                        If LCase(words(i)) = "and" Or LCase(words(i)) = "order" Then Exit For
                        If InStr(words(i), "=") > 0 Or InStr(words(i), ":") > 0 Or InStr(words(i), "(") > 0 Then
                            columnName = Trim(Split(words(i), "=")(0))
                            columnName = Split(columnName, "(")(UBound(Split(columnName, "(")))
                            startPos = InStr(1, sqlText, columnName, vbTextCompare)
                            If startPos > 0 Then
                                With cell.Characters(Start:=startPos, Length:=Len(columnName)).Font
                                    .Size = 16
                                    .Bold = True
                                End With
                            End If
                        End If
                    Next i
                    posAnd = nextPos
                Else
                    Exit Do
                End If
            Loop
        End If
    Next cell

    MsgBox "WHERE 및 AND 조건의 컬럼 서식 변경 완료!"
End Sub