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

by

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

enter image description here

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

enter image description here

EDIT (10/12/2013)

To count cells with specific backcolor see THIS link

Leave a Comment

tech