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

moppyau

New member
Joined
May 6, 2013
Messages
4
Reaction score
0
Points
0
Location
Australia
Excel Version(s)
Excel2013
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
Joined
Mar 21, 2011
Messages
1,942
Reaction score
0
Points
36
Excel Version(s)
O365
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
Joined
May 6, 2013
Messages
4
Reaction score
0
Points
0
Location
Australia
Excel Version(s)
Excel2013
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
Joined
Feb 10, 2012
Messages
190
Reaction score
0
Points
0
Location
Utah, USA
Excel Version(s)
Version 2002 Build 12527.20194
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.
The changes I made to your sheet.
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
Joined
Feb 10, 2012
Messages
190
Reaction score
0
Points
0
Location
Utah, USA
Excel Version(s)
Version 2002 Build 12527.20194
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.
 
Top