# Count the highest number of consecutive cells across a row with the same font color

#### moppyau

##### New member
Hi All,
I have a spreadsheet using Office 2007 that is conditionally formatted depending on cell value. Is it possible to use a formula to count the highest number of consecutive cells across a row with the same font color, blue in my case. I have attached a small sample of my data. Thank-you in advance.
Moppy

#### Attachments

• Book1.xlsx
10.3 KB · Views: 27

#### Bob Phillips

##### Super Moderator
Staff member
You have different conditions returning the same colour in different areas, so I cannot see this as solvable. If you can come up with one rule that determines if blue or not, one for red, etc, it might be doable.

#### moppyau

##### New member
Hi Bob. It's actually a golf score spreadsheet so blue means par. Not every hole has the same par so I can't change much. Thanks for taking the time to have a look anyway.
Cheers

#### Simi

##### New member
View attachment Book1-Simi.xlsm

I am no expert so I have a round about way to do this. There may be a cleaner more efficient way to do this.
Row 1 is now used for temporary calculations. this row could be hidden.
Row 2 is the par value for the given hole.
I assumed blue was par 0, red birdie -1, purple eagle -2.

I created a table to figure out how many of each value there are in a row.
and return the max() from each set.
I then display the max number in priority of purple > red > blue.
Thus getting the data as you described.

There is a macro. to run.
it runs on the active sheet, and automatically counts how many rows of scores you have and loops through them.

Let me know if this helps you.

Simi

Last edited:

#### Simi

##### New member
I tried first to use code to figure what the font color was, however because of the conditional formatting all of the cells actually return the same font.colorindex number. But since you said this was golf, I figured the color isn't the important thing to count.