找到一个用于Excel中判断高亮单元格的用户自定义函数,在这里分享下。
利用这个函数,就能根据单元格的颜色来自动处理Excel数据了。例如,ColorIndex(A1) 返回单元格的颜色值。
Excel 2007 中, 增加用户自定义函数在: 开发工具(Developer) -> Visual Basic 编辑器(Visual Basic Editor) -> 插入(Insert) -> 模块(Module)
'--------------------------------------------------------------------- ' ColorIndex Function '--------------------------------------------------------------------- ' Function: Returns the colorindex of the supplied range ' Synopsis: Initially, gets a colorindex value for black and white ' from the activeworkbook colour palette ' Then works through each cell in the supplied range and ' determines the colorindex, and adds to array ' Finishes by returning acumulated array ' Variations: Determines cell colour (interior) or text colour (font) ' Default is cell colour ' Constraints: Does not count colours set by conditional formatting '--------------------------------------------------------------------- ' Author: Bob Phillips ' Additions for ranges suggested by Harlan Grove '--------------------------------------------------------------------- '--------------------------------------------------------------------- Function ColorIndex(rng As Range, _ Optional text As Boolean = False) As Variant '--------------------------------------------------------------------- Dim cell As Range, row As Range Dim i As Long, j As Long Dim iWhite As Long, iBlack As Long Dim aryColours As Variant If rng.Areas.Count > 1 Then ColorIndex = CVErr(xlErrValue) Exit Function End If iWhite = WhiteColorindex(rng.Worksheet.Parent) iBlack = BlackColorindex(rng.Worksheet.Parent) If rng.Cells.Count = 1 Then If text Then aryColours = DecodeColorIndex(rng, True, iBlack) Else aryColours = DecodeColorIndex(rng, False, iWhite) End If Else aryColours = rng.Value i = 0 For Each row In rng.Rows i = i + 1 j = 0 For Each cell In row.Cells j = j + 1 If text Then aryColours(i, j) = _ DecodeColorIndex(cell,True,iBlack) Else aryColours(i, j) = _ DecodeColorIndex(cell,False,iWhite) End If Next cell Next row End If ColorIndex = aryColours End Function '--------------------------------------------------------------------- Private Function WhiteColorindex(oWB As Workbook) '--------------------------------------------------------------------- Dim iPalette As Long WhiteColorindex = 0 For iPalette = 1 To 56 If oWB.Colors(iPalette) = &HFFFFFF Then WhiteColorindex = iPalette Exit Function End If Next iPalette End Function '--------------------------------------------------------------------- Private Function BlackColorindex(oWB As Workbook) '--------------------------------------------------------------------- Dim iPalette As Long BlackColorindex = 0 For iPalette = 1 To 56 If oWB.Colors(iPalette) = &H0 Then BlackColorindex = iPalette Exit Function End If Next iPalette End Function '--------------------------------------------------------------------- Private Function DecodeColorIndex(rng As Range, _ text As Boolean, _ idx As Long) '--------------------------------------------------------------------- Dim iColor As Long If text Then iColor = rng.font.ColorIndex Else iColor = rng.Interior.ColorIndex End If If iColor < 0 Then iColor = idx End If DecodeColorIndex = iColor End Function '--------------------------------------------------------------------- ' End of ColorIndex Function '---------------------------------------------------------------------