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

最新推荐文章于 2025-11-09 11:20:52 发布

最新推荐文章于 2025-11-09 11:20:52 发布 · 82 阅读

文章标签：

#C #C++ #C#

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

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