一、案例概述:
本次案例主要实现对每一列后五的数据标记颜色,最后统计每一行颜色标记的次数。
二、案例如下:
(1)源数据:
(2) 代码部分:
①对指定列进行标记颜色
Sub 标色()
Dim a As Integer
a = InputBox("请输入要统计的列号")
Cells(1, a).Sort Key1:=Sheet1.Cells(1, a), order1:=xlAscending, Header:=xlYes
For b = 2 To 6
Cells(b, a).Interior.Color = RGB(255, 0, 0)
Next
End Sub
②统计每一行颜色的次数
Sub 统计颜色次数()
Dim K, b, c, d, i, j As Long
'将A3单元格颜色复制给a
K = Sheet1.Range("AG1").Interior.Color
For i = 2 To 42
c = 0
d = 0
For j = 2 To 32
If Sheet1.Cells(i, j).Interior.Color = K Then
c = c + 1
Sheet1.Cells(i, 33) = c
End If
If Len(Trim(Sheet1.Cells(i, 33).Value)) = 0 Then
Sheet1.Cells(i, 33).Value = 0
End If
Next
Next
End Sub
③清空颜色和统计数据
Sub 清空()
Sheet1.Range("B2:AF43").Interior.Color = RGB(255, 255, 255)
Sheet1.Range("AG2:AG43").Clear
End Sub
(3)结果呈现:
①颜色标记和次数统计
②清空颜色和次数统计