|
楼主 |
发表于 2017-12-28 16:00
|
显示全部楼层
Sub FillColor()
Dim IsSort As Boolean, i, j, k
Application.ScreenUpdating = False
IsSort = False '是否排序(手动指定)
k = Range("d:d").Column '指定列(手动指定)
If IsSort Then
Call FillSort(k)
Else
i = Range("a1").CurrentRegion.Rows.Count
j = Range("a1").CurrentRegion.Columns.Count + 1 '辅助列(手动指定)
'1)记录
Cells(1, j) = "辅助列"
Cells(2, j) = 1
Cells(2, j).AutoFill Destination:=Range(Cells(2, j), Cells(i, j)), Type:=xlFillSeries
'2)改变
Call FillSort(k)
'3)恢复
Range("a1").CurrentRegion.Sort key1:=Cells(1, j), order1:=xlAscending, Header:=xlYes
Columns(j).Delete
End If
End Sub
'填色并排序
Sub FillSort(k)
Dim A, u, i
Range("a1").CurrentRegion.Sort key1:=Cells(1, k), order1:=xlAscending, Header:=xlYes 'j列升序
A = Range("a1").CurrentRegion
u = UBound(A)
For i = UBound(A) To 2 Step -1
If A(i, k) <> A(i - 1, k) Then
Range(Cells(i, 1), Cells(u, UBound(A, 2))).Interior.Color = RGB(Rnd * 56 + 200, Rnd * 56 + 200, Rnd * 56 + 200)
u = i - 1
End If
Next i
End Sub
'重置
Sub Reset()
Range("a2:e10").Interior.Color = xlNone
Range("d2:d10") = "=char(int(RAND()*3)+65)"
Range("d2:d10") = Range("d2:d10").Value
End Sub
按某列填色4.rar
(12.01 KB, 下载次数: 0)
|
|