Option Explicit
Sub 내선번호()
Dim rngS As Variant
Dim rngFA As Variant
Dim rngFD As Variant
Dim rngFG As Variant
Dim rngFJ As Variant
Dim i As Long
Dim j As Long
Dim arrA(), arrD(), arrG(), arrJ()
color_index
' 색칠하기@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
'Dim rngColor As Range
'Dim cC As Range
'Dim ii As Variant
'Dim jj As Variant
'Sheets("전체").Activate
'Set rngColor = Sheets("전체").Range("A2", Cells(Rows.Count, "A").End(xlUp))
'rngColor.Offset(, 5).ClearContents
'For Each cC In rngColor
' cC.Offset(, 5) = cC.Interior.ColorIndex
'Next
Sheets("표").Activate
Sheets("표").UsedRange.Borders.LineStyle = xlLineStyleNone ' 기존 라인을 삭제한다.
Sheets("표").UsedRange.Interior.Color = xlNone ' 색 지우기
'@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
Sheets("표").Range("B2", Sheets("표").Cells(Rows.Count, "C").End(xlUp)).ClearContents
Sheets("표").Range("E2", Sheets("표").Cells(Rows.Count, "F").End(xlUp)).ClearContents
Sheets("표").Range("H2", Sheets("표").Cells(Rows.Count, "I").End(xlUp)).ClearContents
Sheets("표").Range("K2", Sheets("표").Cells(Rows.Count, "L").End(xlUp)).ClearContents
rngS = Sheets("전체").Range("A2", Sheets("전체").Cells(Rows.Count, "F").End(xlUp))
rngFA = Sheets("표").Range("A2", Sheets("표").Cells(Rows.Count, "A").End(xlUp))
rngFD = Sheets("표").Range("D2", Sheets("표").Cells(Rows.Count, "D").End(xlUp))
rngFG = Sheets("표").Range("G2", Sheets("표").Cells(Rows.Count, "G").End(xlUp))
rngFJ = Sheets("표").Range("J2", Sheets("표").Cells(Rows.Count, "J").End(xlUp))
'Debug.Print UBound(rngS, 1) '행 - 1차원
'Debug.Print UBound(rngS, 2) '열 - 2차원
ReDim arrA(1 To UBound(rngFA, 1), 1 To 2)
For i = 1 To UBound(rngFA, 1)
For j = 1 To UBound(rngS, 1)
If rngFA(i, 1) = rngS(j, 1) Then
' sheets("표").cells(i,"B") = rngs(j,2)
arrA(i, 1) = rngS(j, 2)
arrA(i, 2) = rngS(j, 3)
' If rngS(j, 6) = 6 Then Sheets("표").Range(Cells(i + 1, "A"), Cells(i + 1, "C")).Interior.ColorIndex = 6
' If rngS(j, 6) = 40 Then Sheets("표").Range(Cells(i + 1, "A"), Cells(i + 1, "C")).Interior.ColorIndex = 40
' If rngS(j, 6) = 43 Then Sheets("표").Range(Cells(i + 1, "A"), Cells(i + 1, "C")).Interior.ColorIndex = 43
' If rngS(j, 6) = 44 Then Sheets("표").Range(Cells(i + 1, "A"), Cells(i + 1, "C")).Interior.ColorIndex = 44
End If
Next
Next
ReDim arrD(1 To UBound(rngFD, 1), 1 To 2)
For i = 1 To UBound(rngFD, 1)
For j = 1 To UBound(rngS, 1)
If rngFD(i, 1) = rngS(j, 1) Then
' sheets("표").cells(i,"B") = rngs(j,2)
arrD(i, 1) = rngS(j, 2)
arrD(i, 2) = rngS(j, 3)
' If rngS(j, 6) = 6 Then Sheets("표").Range(Cells(i + 1, "D"), Cells(i + 1, "F")).Interior.ColorIndex = 6
' If rngS(j, 6) = 40 Then Sheets("표").Range(Cells(i + 1, "D"), Cells(i + 1, "F")).Interior.ColorIndex = 40
' If rngS(j, 6) = 43 Then Sheets("표").Range(Cells(i + 1, "D"), Cells(i + 1, "F")).Interior.ColorIndex = 43
' If rngS(j, 6) = 44 Then Sheets("표").Range(Cells(i + 1, "D"), Cells(i + 1, "F")).Interior.ColorIndex = 44
End If
Next
Next
ReDim arrG(1 To UBound(rngFG, 1), 1 To 2)
For i = 1 To UBound(rngFG, 1)
For j = 1 To UBound(rngS, 1)
If rngFG(i, 1) = rngS(j, 1) Then
' sheets("표").cells(i,"B") = rngs(j,2)
arrG(i, 1) = rngS(j, 2)
arrG(i, 2) = rngS(j, 3)
' If rngS(j, 6) = 6 Then Sheets("표").Range(Cells(i + 1, "G"), Cells(i + 1, "I")).Interior.ColorIndex = 6
' If rngS(j, 6) = 40 Then Sheets("표").Range(Cells(i + 1, "G"), Cells(i + 1, "I")).Interior.ColorIndex = 40
' If rngS(j, 6) = 43 Then Sheets("표").Range(Cells(i + 1, "G"), Cells(i + 1, "I")).Interior.ColorIndex = 43
' If rngS(j, 6) = 44 Then Sheets("표").Range(Cells(i + 1, "G"), Cells(i + 1, "I")).Interior.ColorIndex = 44
End If
Next
Next
ReDim arrJ(1 To UBound(rngFJ, 1), 1 To 2)
For i = 1 To UBound(rngFJ, 1)
For j = 1 To UBound(rngS, 1)
If rngFJ(i, 1) = rngS(j, 1) Then
' sheets("표").cells(i,"B") = rngs(j,2)
arrJ(i, 1) = rngS(j, 2)
arrJ(i, 2) = rngS(j, 3)
' If rngS(j, 6) = 6 Then Sheets("표").Range(Cells(i + 1, "J"), Cells(i + 1, "L")).Interior.ColorIndex = 6
' If rngS(j, 6) = 40 Then Sheets("표").Range(Cells(i + 1, "J"), Cells(i + 1, "L")).Interior.ColorIndex = 40
' If rngS(j, 6) = 43 Then Sheets("표").Range(Cells(i + 1, "J"), Cells(i + 1, "L")).Interior.ColorIndex = 43
' If rngS(j, 6) = 44 Then Sheets("표").Range(Cells(i + 1, "J"), Cells(i + 1, "L")).Interior.ColorIndex = 44
End If
Next
Next
Sheets("표").Range("B2").Resize(UBound(arrA, 1), 2) = arrA
Sheets("표").Range("E2").Resize(UBound(arrD, 1), 2) = arrD
Sheets("표").Range("H2").Resize(UBound(arrG, 1), 2) = arrG
Sheets("표").Range("K2").Resize(UBound(arrJ, 1), 2) = arrJ
Sheets("표").Range("A2").Resize(UBound(arrA, 1), 3).Borders.LineStyle = xlDot
Sheets("표").Range("D2").Resize(UBound(arrD, 1), 3).Borders.LineStyle = xlDot
Sheets("표").Range("G2").Resize(UBound(arrG, 1), 3).Borders.LineStyle = xlDot
Sheets("표").Range("J2").Resize(UBound(arrJ, 1), 3).Borders.LineStyle = xlDot
Columns("A").ColumnWidth = 12
Columns("D").ColumnWidth = 12
Columns("G").ColumnWidth = 12
Columns("J").ColumnWidth = 12
Columns("B").ColumnWidth = 7
Columns("E").ColumnWidth = 7
Columns("H").ColumnWidth = 7
Columns("K").ColumnWidth = 7
Columns("C").ColumnWidth = 13.75
Columns("F").ColumnWidth = 13.75
Columns("I").ColumnWidth = 13.75
Columns("L").ColumnWidth = 13.75
Call color_find
Sheets("표").Range("A1") = "안녕하십니까 주식회사 ○○○ ●●●팀 △△△입니다"
Range("L1") = Date
End Sub
Sub 색상배열Find()
Dim rngF As Range
Dim cfF As Range
Dim adF As String
Dim arrNum()
Dim arrColor6()
Dim arrColor40()
Dim arrColor43()
Dim arrColor44()
Dim i As Long
Dim i6 As String
Sheets("전체").Activate
Set rngF = Range("F2", Cells(Rows.Count, "F").End(xlUp))
ReDim arrNum(0 To 3) As Variant
arrNum = Array(6, 40, 43, 44)
For i = 0 To UBound(arrNum)
Set cfF = rngF.Find(arrNum(i), , , xlWhole)
If Not cfF Is Nothing Then
adF = cfF.Address '처음주소
If cfF = 6 Then
Do
ReDim Preserve arrColor6(i)
arrColor6(i6) = cfF.Offset(, -5)
i6 = i6 + 1
Loop Until cfF.Address = adF
End If
End If
Next
End Sub
Sub 동적배열()
Dim rng As Range
Dim r As Range
Dim k
Dim c As Range
Dim adr() ' 동적배열
Dim arr6() ' 동적배열
Dim arr40() ' 동적배열
Dim arr43() ' 동적배열
Dim arr44() ' 동적배열
Dim i As Long
Dim j As Long
Dim i6 As Long
Dim i40 As Long
Dim i43 As Long
Dim i44 As Long
Dim colorNum()
Sheets("전체").Activate
Set rng = Sheets("전체").Range("F2", Cells(Rows.Count, "F").End(xlUp))
ReDim colorNum(0 To 3)
colorNum = Array(6, 40, 43, 44)
For Each r In rng ' 선택된 다중범위
If r = 6 Then
ReDim Preserve arr6(i6)
arr6(i6) = r.Offset(, -5)
i6 = i6 + 1
End If
If r = 40 Then
ReDim Preserve arr40(i40)
arr40(i40) = r.Offset(, -5)
i40 = i40 + 1
End If
If r = 43 Then
ReDim Preserve arr43(i43)
arr43(i43) = r.Offset(, -5)
i43 = i43 + 1
End If
If r = 44 Then
ReDim Preserve arr44(i44)
arr44(i44) = r.Offset(, -5)
i44 = i44 + 1
End If
Next
Sheets("표").Activate
Sheets("표").UsedRange.Interior.Color = xlNone ' 색 지우기
For i = 0 To UBound(arr6)
For j = 2 To Sheets("표").Cells(Rows.Count, "A").End(xlUp).Row
If Cells(j, "A") = arr6(i) Then Sheets("표").Range(Cells(j, "A"), Cells(j, "C")).Interior.ColorIndex = 6
Next
For j = 2 To Sheets("표").Cells(Rows.Count, "D").End(xlUp).Row
If Cells(j, "D") = arr6(i) Then Sheets("표").Range(Cells(j, "D"), Cells(j, "F")).Interior.ColorIndex = 6
Next
For j = 2 To Sheets("표").Cells(Rows.Count, "G").End(xlUp).Row
If Cells(j, "G") = arr6(i) Then Sheets("표").Range(Cells(j, "G"), Cells(j, "I")).Interior.ColorIndex = 6
Next
For j = 2 To Sheets("표").Cells(Rows.Count, "J").End(xlUp).Row
If Cells(j, "J") = arr6(i) Then Sheets("표").Range(Cells(j, "J"), Cells(j, "L")).Interior.ColorIndex = 6
Next
Next
For i = 0 To UBound(arr40)
For j = 2 To Sheets("표").Cells(Rows.Count, "A").End(xlUp).Row
If Cells(j, "A") = arr40(i) Then Sheets("표").Range(Cells(j, "A"), Cells(j, "C")).Interior.ColorIndex = 40
Next
For j = 2 To Sheets("표").Cells(Rows.Count, "D").End(xlUp).Row
If Cells(j, "D") = arr40(i) Then Sheets("표").Range(Cells(j, "D"), Cells(j, "F")).Interior.ColorIndex = 40
Next
For j = 2 To Sheets("표").Cells(Rows.Count, "G").End(xlUp).Row
If Cells(j, "G") = arr40(i) Then Sheets("표").Range(Cells(j, "G"), Cells(j, "I")).Interior.ColorIndex = 40
Next
For j = 2 To Sheets("표").Cells(Rows.Count, "J").End(xlUp).Row
If Cells(j, "J") = arr40(i) Then Sheets("표").Range(Cells(j, "J"), Cells(j, "L")).Interior.ColorIndex = 40
Next
Next
For i = 0 To UBound(arr43)
For j = 2 To Sheets("표").Cells(Rows.Count, "A").End(xlUp).Row
If Cells(j, "A") = arr43(i) Then Sheets("표").Range(Cells(j, "A"), Cells(j, "C")).Interior.ColorIndex = 43
Next
For j = 2 To Sheets("표").Cells(Rows.Count, "D").End(xlUp).Row
If Cells(j, "D") = arr43(i) Then Sheets("표").Range(Cells(j, "D"), Cells(j, "F")).Interior.ColorIndex = 43
Next
For j = 2 To Sheets("표").Cells(Rows.Count, "G").End(xlUp).Row
If Cells(j, "G") = arr43(i) Then Sheets("표").Range(Cells(j, "G"), Cells(j, "I")).Interior.ColorIndex = 43
Next
For j = 2 To Sheets("표").Cells(Rows.Count, "J").End(xlUp).Row
If Cells(j, "J") = arr43(i) Then Sheets("표").Range(Cells(j, "J"), Cells(j, "L")).Interior.ColorIndex = 43
Next
Next
For i = 0 To UBound(arr44)
For j = 2 To Sheets("표").Cells(Rows.Count, "A").End(xlUp).Row
If Cells(j, "A") = arr44(i) Then Sheets("표").Range(Cells(j, "A"), Cells(j, "C")).Interior.ColorIndex = 44
Next
For j = 2 To Sheets("표").Cells(Rows.Count, "D").End(xlUp).Row
If Cells(j, "D") = arr44(i) Then Sheets("표").Range(Cells(j, "D"), Cells(j, "F")).Interior.ColorIndex = 44
Next
For j = 2 To Sheets("표").Cells(Rows.Count, "G").End(xlUp).Row
If Cells(j, "G") = arr44(i) Then Sheets("표").Range(Cells(j, "G"), Cells(j, "I")).Interior.ColorIndex = 44
Next
For j = 2 To Sheets("표").Cells(Rows.Count, "J").End(xlUp).Row
If Cells(j, "J") = arr44(i) Then Sheets("표").Range(Cells(j, "J"), Cells(j, "L")).Interior.ColorIndex = 44
Next
Next
End Sub
Sub color_find()
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim rng As Range
Dim cf As Range
Dim c As Range
Dim arr(3)
Dim rngA As Range
Dim rngD As Range
Dim rngG As Range
Dim rngJ As Range
Dim rngUnion As Range
Set sh1 = Sheets("전체")
Set sh2 = Sheets("표")
Set rng = sh1.Range("A2", sh1.Cells(Rows.Count, "F").End(xlUp))
Sheets("표").Activate
Sheets("표").UsedRange.Interior.Color = xlNone ' 색 지우기
Set rngA = sh2.Range("A2", sh2.Cells(Rows.Count, "A").End(xlUp))
Set rngD = sh2.Range("D2", sh2.Cells(Rows.Count, "D").End(xlUp))
Set rngG = sh2.Range("G2", sh2.Cells(Rows.Count, "G").End(xlUp))
Set rngJ = sh2.Range("J2", sh2.Cells(Rows.Count, "J").End(xlUp))
Set rngUnion = Application.Union(rngA, rngD, rngG, rngJ)
For Each c In rngUnion
Set cf = rng.Columns(1).Find(c, , , xlWhole)
If cf.Offset(, 5) = 6 Then c.Resize(, 3).Interior.ColorIndex = 6
If cf.Offset(, 5) = 40 Then c.Resize(, 3).Interior.ColorIndex = 40
If cf.Offset(, 5) = 43 Then c.Resize(, 3).Interior.ColorIndex = 43
If cf.Offset(, 5) = 44 Then c.Resize(, 3).Interior.ColorIndex = 44
Next
'For Each c In rngA
' Set cf = rng.Columns(1).Find(c, , , xlWhole)
' If cf.Offset(, 5) = 6 Then c.Resize(, 3).Interior.ColorIndex = 6
' If cf.Offset(, 5) = 40 Then c.Resize(, 3).Interior.ColorIndex = 40
' If cf.Offset(, 5) = 43 Then c.Resize(, 3).Interior.ColorIndex = 43
' If cf.Offset(, 5) = 44 Then c.Resize(, 3).Interior.ColorIndex = 44
'Next
'For Each c In rngD
' Set cf = rng.Columns(1).Find(c, , , xlWhole)
' If cf.Offset(, 5) = 6 Then c.Resize(, 3).Interior.ColorIndex = 6
' If cf.Offset(, 5) = 40 Then c.Resize(, 3).Interior.ColorIndex = 40
' If cf.Offset(, 5) = 43 Then c.Resize(, 3).Interior.ColorIndex = 43
' If cf.Offset(, 5) = 44 Then c.Resize(, 3).Interior.ColorIndex = 44
'Next
'For Each c In rngG
' Set cf = rng.Columns(1).Find(c, , , xlWhole)
' If cf.Offset(, 5) = 6 Then c.Resize(, 3).Interior.ColorIndex = 6
' If cf.Offset(, 5) = 40 Then c.Resize(, 3).Interior.ColorIndex = 40
' If cf.Offset(, 5) = 43 Then c.Resize(, 3).Interior.ColorIndex = 43
' If cf.Offset(, 5) = 44 Then c.Resize(, 3).Interior.ColorIndex = 44
'Next
'For Each c In rngJ
' Set cf = rng.Columns(1).Find(c, , , xlWhole)
' If cf.Offset(, 5) = 6 Then c.Resize(, 3).Interior.ColorIndex = 6
' If cf.Offset(, 5) = 40 Then c.Resize(, 3).Interior.ColorIndex = 40
' If cf.Offset(, 5) = 43 Then c.Resize(, 3).Interior.ColorIndex = 43
' If cf.Offset(, 5) = 44 Then c.Resize(, 3).Interior.ColorIndex = 44
'Next
End Sub
Sub color_index()
Dim sh1 As Worksheet
Dim rngA As Range
Dim c As Range
Set sh1 = Sheets("전체")
Set rngA = sh1.Range("A2", sh1.Cells(Rows.Count, "A").End(xlUp))
rngA.Offset(, 5).ClearContents
For Each c In rngA
c.Offset(, 5) = c.Interior.ColorIndex
Next
End Sub