Excel-User.com

When we have colors on our sheets data and want, for instance, to count how many “red” words we have on our sheet, that is not possible because there is no formula in Excel to check for font colors. Instead we can create our own VBA Function to get the font color. It’s a very simple code. You have to insert it on a VBA module on your sheet.

Function GetFontColor(ByVal Target As Range) As Object
    GetFontColor = Target.Font.ColorIndex
End Function

Then you can use it on your sheet like this:

GETFONTCOLOR(A2)

Below is an example on how you can use this function. In column C we put the font color of text in column A.

To count the number of “red” words in column A we can simple to this:

COUNTIF(C2:C9,3)

“3” in the formula refers to the color red.

One of the most common tasks that an Excel user needs to do is to find duplicates on a list of data. There are several ways to do this, depending on the needs. One of the ways is to highlight the values that are duplicated on a column. This can be done using the Format-Conditional Formatting option on the menu bar. Here's an example:


On cell A2, I created a Conditional Formatting with a condition "Formula is" and putted the function =COUNTIF(A:A,A2)>1. The COUNTIF function has the following syntax:

COUNTIF(range,criteria)

range is the area where you you want to check if it finds the value specified by criteria.

This will return TRUE if the value from cell A2 is found more than 1 time in column A. If this is TRUE it will apply the Format that I defined. I then Copy and Paste Special-Format to the rest of the cells in column A.

In Excel 2007 and 2010, you can use the a new option on the Conditional Formatting menu that is the Duplicate Values.


Just select the range where you want to highlight the duplicate and go to the menu bar and choose Highlight Cells Rules-Duplicate Values. A Duplicate Values dialog box will open. You can choose to highlight the Duplicate or the Unique values and you can set the color that you want to use to highlight the values.

The result will be the same as using the COUNTIF solution. On Excel 2003 you don’t have this Duplicate Values option on the Conditional Formatting menu.