If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
is there a formula that can count a range of cells with text?
Hi
to count colore cells below a repost: ---- using a procedure from Bob Phillips and Harlan Grove you may try the following: =SUMPRODUCT(--(ColorIndex(A1:A100)=3)) to count all red cells (background color) within the range A1:A100 or =SUMPRODUCT(--(ColorIndex(A1:A100,TRUE)=3)) to count all red cells (font color) within the range A1:A100 To get the colorindex of a specific cell use =ColorIndex(A1) Adapt this to your requirements ------ 'Code to paste in one of your modules '--------------------------------------------------------------------- Function ColorIndex(rng As Range, _ Optional text As Boolean = False) As Variant '--------------------------------------------------------------------- ' Function: Returns the colorindex of the supplied range ' Synopsis: ' Author: Bob Phillips/Harlan Grove ' '--------------------------------------------------------------------- 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 -- Regards Frank Kabel Frankfurt, Germany Is there a formula that counts # of cells selected? What if I have a range of cells and some of them have blue as the bgrnd or a "format", is there a specific formula that can be used to find out how many have this style? Thanks! --- Message posted from http://www.ExcelForum.com/ |
Thread Tools | |
Display Modes | |
|
|