countif text color = red 3

[mbowler9]

Hello all. I am new to the Office/Excel board, but have been around the MS Access board for a while. I am curious if there is a way to do a countif on text color and/or cell color in Excel 2002. The cells/text were manually formatted, so there isn't a criteria that I can plug in to the countif.

My workbook has around 20 worksheets, each with around 30 columns and 100 rows of info.

Thanks

 

[SkipVought]

Hi,

Nope, however, here's a macro you could run on each sheet

Sub CountRedCells()
    i = 0
    For Each c In ActiveSheet.UsedRange
       If c.Interior.Color = vbRed Then i = 1 + 1
    Next
    MsgBox i
End Sub

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[luceze]

Excel doesn't store any useable color data in the cell . You can however use a vba routine to assign a value in another cell.

For example:
Sub getBackcolor()
For Each cell In Selection
cell.Offset(0, 1) = cell.Interior.ColorIndex
Next
End Sub

This will put the color index in a cell to the right of your selection. You can then use count if off that column.

HTH,
Eric

 

[SkipVought]

OOPS I meant to state

If c.Interior.Color = vbRed Then i = i + 1

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[PeterMoran]

Hi mbowler9,

Chip Pearson has some good stuff on counting colours at:

http://www.cpearson.com/excel/colors.htm

It may add to what has already been provided.

Good Luck!
Peter Moran

 

[xlbo]

If you want a function to return a value, it'd have to loop thru all the rows - and therefore be slow for recalcs.
Summat like this should work

Function CountRed(rng as range)
dim ctr as long
ctr = 0
for each c in rng
if c.interior.colorindex = vbred then ctr = ctr + 1
next
CountRed = ctr
end function

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[mbowler9]

Thanks. PeterMoran and xlbo. Your posts proved to be most helpful.