Conditional Formatting to count specific cell colors

[sheilakhull]

I have a spread sheet with the units names for different areas. Some are in red font, some are in black font. I need to count for each name for when it's listed in red & then again if black. Anyone have a solution? Thanks!

 

[martinee]

Ouch, not really. If you were looking for a formula, I think maybe you are out of luck. Try a UDF or something of that nature perhaps. Post back if you need help with the UDF. HTH.

 

[Barry Katcher]

Hi, Sheila

What are the conditions ("formula is" or "cell value is") that you used to set your conditional formatting? Can you use these same conditions as the criteria in your COUNTIF formula?

For instance, in the example below, I conditionally formatted A1:A6 to be a red font if 100 or over, and to be a black font if less than 100. I then used the same conditions as criteria in the COUNTIF formulas in cells A8 & A9 (see formulas to the right).

Book1
	A	B	C	D
1	50
2	75
3	100
4	45
5	140
6	60
7
8	4	=COUNTIF(A1:A6,"<100")
9	2	=COUNTIF(A1:A6,">=100")
Sheet1

Barry

 

[sheilakhull]

Barry,
The spreadsheet was set up by someone else. They used Red font if that person was primary for the specific subject, black if they were secondary. I can do a simple formula to count the specific name but I want to count that specific name if it's in red & then again in black. Is there a way without VBA? I don't know VBA & need to do this with in the next week.

Also, the spreadsheet is set up to read by row, not columns.

 

[Barry Katcher]

...Red font if that person was primary for the specific subject, black if they were secondary.

Select one of the conditionally formatted cells, go int Format>Conditional Format, and see what the formula or cell content criteria is, and post it back in a reply. We may be able to use it as I described above.

Also, the spreadsheet is set up to read by row, not columns.

This should not matter, the range that you set in the COUNTIF formula determines the search; it can be column-ular or row-ular.

If this doesn't work, you might want to search the Board for something like "font color" or "color number". (EDIT: there ya' go; HTH just gave you a UDF that you can use) I tried the =CELL(), which returns a cell's properties, but font color was not one of the properties it returned.

Barry

 

[martinee]

This would incorporate some VBA, but I've done the work for you here. Here is a UDF:

Book1
	A	B	C	D
1	dsfg
2	dsfg
3	dsfg		7
4	dsfg		3
5	dsfg
6	dsfg
7	dsfg
8	dsfg
9	dsfg
10	dsfg
Sheet1

Here is the code:

Public Function CountColor(rng As Range, COLOR As String) As Long
'Precondition: rng must be a valid, non-empty range, and
'color must be either BLACK or RED
'Postcondition: Function counts the number of cells which have font color
'as specified by the 'COLOR' argument, and returns value to the cell containing
'the formula.

'declarations
Dim c As Range
Dim cindex As Integer, x As Long

'correct for appropriate case sensitivity
COLOR = UCase(COLOR)
For Each c In rng

Select Case COLOR
Case "RED"
    If c.Font.ColorIndex = 3 Then
        x = x + 1
    End If
Case "BLACK"
    If c.Font.ColorIndex = 1 Or c.Font.ColorIndex = xlNone Then
        x = x + 1
    End If
Case Else
    'Catches invalid inputs for the COLOR paramater
    MsgBox "Invalid input, color must be either 'RED' or 'BLACK'."
End Select

Next c

CountColor = x

End Function

HTH.