|
選択セル範囲の色番号と各色番号のセルの数を取得しメッセージボックス
に表示します。
配列は一見複雑ですが、慣れれば難しくはないし便利です。
Sub iroitiran()
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim c As Range
Dim r As Range
Dim iroary() As Variant
Dim myindx As String
Dim bl As Boolean
Dim msgstr As String
Set r = Selection
i = -1
For Each c In r
myindx = c.Interior.ColorIndex
If myindx = xlNone Then myindx = "-"
If i = -1 Then
i = 0
ReDim Preserve iroary(1, 0 To i)
iroary(0, i) = myindx
iroary(1, i) = 1
Else
bl = False
For j = 0 To i
If iroary(0, j) = myindx Then
iroary(1, j) = iroary(1, j) + 1
bl = True
Exit For
End If
Next j
If bl = False Then
i = i + 1
ReDim Preserve iroary(1, 0 To i)
iroary(0, i) = myindx
iroary(1, i) = 1
End If
End If
Next c
For k = 0 To i
If msgstr <> "" Then msgstr = msgstr & vbCrLf
msgstr = msgstr & iroary(0, k) & ":" & iroary(1, k)
Next k
MsgBox msgstr
Erase iroary
End Sub
|
|