请各位大神, 看看这里是错的, 要如何改, 谢谢! 这个vba是把工作表1中第2,5,8,11栏中有颜色的单元格,复制其格式,然后到工作表2第1栏至第5栏找出与工作表1相同的数值, 然后贴上其格式
Sub CopyFormats()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim rng1 As Range, rng2 As Range, cell1 As Range, cell2 As Range
Set ws1 = ThisWorkbook.Worksheets("工作表1")
Set ws2 = ThisWorkbook.Worksheets("工作表2")
For Each rng1 In Array(ws1.Range("B1:B" & ws1.Cells(ws1.Rows.Count, "B").End(xlUp).Row), _
ws1.Range("E1:E" & ws1.Cells(ws1.Rows.Count, "E").End(xlUp).Row), _
ws1.Range("H1:H" & ws1.Cells(ws1.Rows.Count, "H").End(xlUp).Row), _
ws1.Range("K1:K" & ws1.Cells(ws1.Rows.Count, "K").End(xlUp).Row), _
ws1.Range("N1:N" & ws1.Cells(ws1.Rows.Count, "N").End(xlUp).Row))
For Each cell1 In rng1.Cells
If cell1.Interior.ColorIndex <> xlNone Then
Set rng2 = ws2.Range("A1:E" & ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row)
For Each cell2 In rng2.Cells
If cell2.Interior.ColorIndex = cell1.Interior.ColorIndex Then
cell2.Copy
cell1.PasteSpecial xlPasteFormats
Exit For
End If
Next cell2
End If
Next cell1
Next rng1
Application.CutCopyMode = False
End Sub
|