Numbering rows by font color, but skipping blanks

Excel Discussion in 'Microsoft Office' started by JoeyOreally, Jan 10, 2018.

  1. JoeyOreally


    Jan 10, 2018
    Likes Received:
    I'm using Column A of a spreadsheet to number some items in Column B. Is there a way to automatically number down in Column A, while skipping over the blanks and continuing where I left off, and using different font colors for different numbering, i.e.

    1. 1 2Q H1 EE10
      2 2N H1 CC11-2
      3 2Q H1 CC11-2

      1 2E H1 CC11
      2 2N H1 CC11

      4 2Q H1 CC11

      5 4E H1 CC11
      6 4N H1 CC11
      1 4N H1 AA13
      7 2N H1 A13-1

      2 2N H1 A13-2
      8 2E H1 B12-UPS
    JoeyOreally, Jan 10, 2018
    1. Advertisements

  2. JoeyOreally


    Feb 21, 2018
    Likes Received:
    Very interesting query...!

    Please use this function. Copy paste it in a VBA module
    Please remember it is intended to work for having ColorCode formula in column a as follows:
    to be written in cell A5 and copied down to as many rows as you need

    Function ColorCode(rng2 As Range)

    On Error Resume Next
    If Range(rng2.Address) = "" Then
    ColorCode = ""
    ColorCode = Format(rng2.Font.ColorIndex, "000") & "-" & Application.CountIf(Range("A1:A" & rng2.Row - 1), Format(rng2.Font.ColorIndex, "000") & "-*") + 1

    End If
    On Error GoTo 0

    End Function

    If you don't need first Four characters in colorcode you can value-paste then split. or use right() function to pick the seriel number only.
    Last edited: Mar 1, 2018
    AmjiBhai, Mar 1, 2018
    Becky likes this.
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.