Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
A macro that counts cells when the conditional formatting formula =True. i.e. the cell is colored other than white.
I have a macro I got from someone that counts colored cells.
Unfortunately, it doesn't work with conditional formatting. Does anyone know how to solve this so that I can run a macro that would include conditional formatting? I am trying to determine the number of cells in a row where the conditional formatting formula equals true. '--------------------------------------------------------------------- Function ColorIndex(rng As Integer, _ Optional text As Boolean = True) '--------------------------------------------------------------------- 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 Any help would be appreciated.. |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional formatting a #N/A return in a cell? I want it white. | Excel Discussion (Misc queries) | |||
Colored cells are showing white on screen, want to see color? | Excel Discussion (Misc queries) | |||
Count the number of times a cell is colored with conditional formatting | Excel Programming | |||
Count the number of times a cell is colored with conditional formatting | Excel Programming | |||
Cell right next to colored cells is automatically colored on entering a value | Excel Programming |