|
求VB代码!!!!感谢。要求:SHEET1对应Sheet2,SHEET1 A列对应Sheet2 A列填充颜色,B列对应B列填充颜色,C列对应C列填充颜色以此类推,数据较多,需要填充迅速,预计Sheet2每列有60000格,预计共50列,Sheet1提供数值范围(1-100)
单元格逐个判断改色操作效率不可能很高的,我用了变通的方法,代码如下:
- Sub aaa()
- Dim arr, i&, j&, d As Object
- Application.ScreenUpdating = False
- Set d = CreateObject("scripting.dictionary")
- arr = Sheets(1).[a1].CurrentRegion
- For j = 1 To UBound(arr, 2)
- Set d(j) = CreateObject("scripting.dictionary")
- For i = 1 To UBound(arr)
- If arr(i, j) <> "" Then d(j)(arr(i, j)) = ""
- Next i
- Next j
- arr = Sheets(2).[a1].CurrentRegion
- For j = 1 To UBound(arr, 2)
- For i = 1 To UBound(arr)
- If d(j).exists(arr(i, j)) Then arr(i, j) = "=" & arr(i, j)
- Next i
- Next j
- Sheets(2).[a1].CurrentRegion = arr
- Sheets(2).Cells.SpecialCells(xlCellTypeFormulas).Interior.Color = RGB(226, 239, 218)
- Sheets(2).[a1].CurrentRegion = Sheets(2).[a1].CurrentRegion.Value
- Application.ScreenUpdating = True
- End Sub
复制代码
|
|