파란크리스마스

출처 : 엑셀(Excel) 개발도구 탭 추가하기 (리본메뉴에 개발도구 탭 표시)

Sub Hello()

    Dim Sht As Worksheet
    Dim iRow As Integer
    
    For Each Sht In Worksheets
        'Sht.Cells.WrapText = True
        If Sht.Index >= 3 Then
            'MsgBox (Sht.Name)
            Sht.Select
            MsgBox (ActiveSheet.Name)
            For iRow = 7 To ActiveSheet.UsedRange.Rows.Count
            
                If Sht.Cells(iRow, 1) = "마지막행" Then
                    Exit For
                End If
                
                ' 가운데 정렬
                With Sht.Cells(iRow, 6)
                    .HorizontalAlignment = xlCenter
                    .VerticalAlignment = xlCenter
                End With
                With Sht.Cells(iRow, 7)
                    .HorizontalAlignment = xlCenter
                    .VerticalAlignment = xlCenter
                End With
                
                Sht.Cells(iRow, 2).Select
                
                
                ' 노락색 인경우 = 6
                If ActiveCell.Interior.ColorIndex = 6 Then
                    ' 회색으로 변경 = 15
                    Sht.Cells(iRow, 1).Interior.ColorIndex = 15
                    Sht.Cells(iRow, 2).Interior.ColorIndex = 15
                    Sht.Cells(iRow, 3).Interior.ColorIndex = 15
                    Sht.Cells(iRow, 4).Interior.ColorIndex = 15
                    Sht.Cells(iRow, 5).Interior.ColorIndex = 15
                    Sht.Cells(iRow, 6).Interior.ColorIndex = 15
                    Sht.Cells(iRow, 7).Interior.ColorIndex = 15
                    Sht.Cells(iRow, 8).Interior.ColorIndex = 15
                End If
                'MsgBox (Sht.Cells(iRow, 1))
            Next iRow
        End If
    Next Sht
End Sub

Sub Macro1()
'
'  For row = 1 To 1000
'    For roworg = 1 To 1000
'      If CWorksheets("Sheet1").Cells(1, 7).Value
'    Next roworg
'  Next row

  For row = 1 To 1000
    If Cells(row, 2).Value <> "" Then
      If Trim(Cells(row, 2).Value) = Trim(Cells(row, 5).Value) Then
        Cells(row, 6).Value = "일치"
      Else
        If Cells(row, 5).Value <> "" Then
          Cells(row, 6).Value = "불일치"
        Else
          Cells(row, 6).Value = "-"
        End If
      End If
    Else
      Cells(row, 6).Value = ""
    End If
  Next row

End Sub



Sub Macro2()
'
  Dim aaa As Integer

  For i = 1 To 400
    For j = 1 To 400
      If Cells(i, 1).Value = Cells(j, 7).Value Then
        Cells(i, 4).Value = Cells(j, 7).Value
        Cells(i, 5).Value = Cells(j, 8).Value
        Cells(j, 9).Value = "O"
        Exit For
      End If
    Next j
   
    If Cells(i, 1).Value = "" Then
      aaa = i
      Exit For
    End If
  Next i

  For Row = 1 To 400
    If Cells(Row, 2).Value <> "" Then
      If Trim(Cells(Row, 2).Value) = Trim(Cells(Row, 5).Value) Then
        Cells(Row, 6).Value = "일치"
      Else
        If Cells(Row, 5).Value <> "" Then
          If Cells(Row, 5).Value = "1234567890" Then
            Cells(Row, 6).Value = "파일누락"
          Else
            Cells(Row, 6).Value = "불일치"
          End If
        Else
          Cells(Row, 6).Value = "파일누락"
        End If
      End If
    Else
      If Cells(Row, 5).Value <> "" Then
        Cells(Row, 6).Value = "목록누락"
      Else
        Cells(Row, 6).Value = ""
      End If
    End If
  Next Row
 
  For i = 1 To 400
    If Cells(i, 9).Value = "O" Then
    Else
      Cells(aaa, 4).Value = Cells(i, 7).Value
      Cells(aaa, 5).Value = Cells(i, 8).Value
      Cells(aaa, 6).Value = "목록누락"
      aaa = aaa + 1
      If Cells(i, 7).Value = "" Then
        Exit For
      End If
    End If
  Next i

End Sub