Conditional Formatting to count specific cell colors

sheilakhull

Board Regular
Joined
Jan 5, 2005
Messages
95
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!
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

martinee

Well-known Member
Joined
Nov 4, 2003
Messages
960
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

Well-known Member
Joined
Feb 25, 2002
Messages
4,053
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
ABCD
150
275
3100
445
5140
660
7
84=COUNTIF(A1:A6,"<100")
92=COUNTIF(A1:A6,">=100")
Sheet1


Barry
 

sheilakhull

Board Regular
Joined
Jan 5, 2005
Messages
95
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

Well-known Member
Joined
Feb 25, 2002
Messages
4,053
...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

Well-known Member
Joined
Nov 4, 2003
Messages
960
This would incorporate some VBA, but I've done the work for you here. Here is a UDF:
Book1
ABCD
1dsfg
2dsfg
3dsfg7
4dsfg3
5dsfg
6dsfg
7dsfg
8dsfg
9dsfg
10dsfg
Sheet1

Here is the code:
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.
 

Forum statistics

Threads
1,147,665
Messages
5,742,502
Members
423,734
Latest member
123hmMission

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top