Results 1 to 5 of 5

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

  1. #1

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
    Attached Files Attached Files

  2. #2
    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.

  3. #3
    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

  4. #4
    Conjurer Simi's Avatar
    Join Date
    Feb 2012
    Location
    Utah, USA
    Posts
    187
    Articles
    0
    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 by Simi; 2013-05-08 at 08:52 PM. Reason: added the file

  5. #5
    Conjurer Simi's Avatar
    Join Date
    Feb 2012
    Location
    Utah, USA
    Posts
    187
    Articles
    0
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •