Excel Numbering rows by font color, but skipping blanks

Jan 10, 2018
Reaction score
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
Feb 21, 2018
Reaction score
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:

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. After that, you can post your question and our members will help you out.

Ask a Question