A macro to get all interior colorindex has been used in thisworkbook

iteye_19871

于 2006-09-28 23:28:00 发布

阅读量74

点赞数

文章标签： C C++ C#

本文提供两种使用VBA获取Excel工作簿中所有已使用的颜色索引的方法：一种是通过集合，另一种是通过字典。这两种方法均遍历每张工作表的已使用范围，收集颜色索引，并最终显示所有被使用过的颜色索引。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1集合的方法：

Sub getallcolor()
Dim sh As Worksheet, x As New Collection, colors(), c As Range, i As Long
On Error Resume Next
For Each sh In Sheets
For Each c In sh.UsedRange
x.Add c.Interior.ColorIndex, "key" & c.Interior.ColorIndex
Next
Next
ReDim colors(1 To x.Count)
For i = 1 To x.Count
colors(i) = x(i)
Next
MsgBox "The following colorindex has been used in thisworkbook:" & vbCrLf & vbCrLf & Join(colors, vbCrLf)
End Sub

2字典的方法：

Sub getallcolor()
Dim sh As Worksheet, r As Range, mycolor As Integer
With CreateObject("scripting.dictionary")
For Each sh In Sheets
For Each r In sh.UsedRange
mycolor = r.Interior.ColorIndex
If Not .exists(mycolor) Then .Add mycolor, Nothing
Next
Next
MsgBox "The following colorindex has been used in thisworkbook:" & vbCrLf & Join(.keys, vbLf)
End With
End Sub