How to count up text of a different font colour in excel

Question

I have a list of names that has been exported from another database into excel. The names in the list that are of interest are highlighted in red font. I would like a way to count it, i.e. John Smith appears 5 times in total in a column but 3 of the 5 times, his name comes up highlighted in red font. So I would like to see how many instances of his name comes up red.

I know How to search all instances of his name e.g. =COUNTIF(A1:A100,"John Smith ")

I've also had help in creating a VB function which counts all values that are red (=SumRed) (once the colour index is specified) in a worksheet by using this:

Function SumRed(MyRange As Range)
    SumRed = 0
For Each cell In MyRange
    If cell.Font.Color = 255 Then
        SumRed = SumRed + cell.Value
    End If
Next cell
End Function

I just can't find a way to combine the two counting conditions. Any help would be much appreciated!

Solution 1

You don't need VBA for this but still if you want VBA Solution then you can go with any of the other two answers. :)

We can use Excel formula to find the Font Color of a cell. See this example.

We will be using XL4 macros.

1. Open the Name Manager
2. Give a name. Say FontColor
3. Type this formula in Refers To =GET.CELL(24,OFFSET(INDIRECT("RC",FALSE),0,-1)) and click OK

Explanation of the formula

The Syntax is

GET.CELL(type_num, reference)

Type_num is a number that specifies what type of cell information you want.
reference is the cell reference

In the above formula the number 24 gives you the font color of the first character in the cell, as a number in the range 1 to 56. If font color is automatic, returns 0. And Hence the drawback. Ensure that the entire font color is red. We could have used 64 but that is not working properly.

OFFSET(INDIRECT("RC",FALSE),0,-1) refers to the immediate cell on the left.

Now enter this formula in a cell =IF(AND(Fontcolor=3,B1="John Smith"),1,0) and copy it down.

Note: The formula has to be entered on the Right of the cell which contains the Text.

Screentshot

EDIT (10/12/2013)

To count cells with specific backcolor see THIS link

Solution 2

I think you're almost there but this deserves another function @user bet me to the punch line :(

Function CoundRedAndText(MyRange As Range, Mytext as string) as long
    CoundRedAndText = 0
    For Each cell In MyRange
        If cell.Font.Color = 255 and cell.value like MyText Then
            CoundRedAndText = CoundRedAndText + 1 'you had cell.value but dont know why?
        End If
    Next cell
End Function

Usage, =CountRedAndText(A1:A25, "John Smith")

Comments

[glh]

+1 for this elegant and detailed alternative to vba. You learn something new every day.

[user2140261]

I feel as though this is a very un-useful way to does this. And it seems it takes ALOT of ahead of time set up. Getting all the data and make sure you can move the data around, insert a column next to the name or move name to the end of columns in order to place this in it. Also seems like it would be extremely slow if you had say 500,000 records to check. Also my biggest fault is re-usability, what if in the future OP needed a count of each unique name and color combination this would take a long time to execute and debug would it not?

[Unsliced]

+1 - For finding the font colour (and background colour), this is exactly what I was looking for - allowing me to write a function based on the colour, but without resorting to VBA. Many thanks.