How to add Auto Numbering in Excel

Hi Experts

I have an excel sheet in which there are 2 types of Rows. The green colored rows are the MAIN CATEGORY Rows and each of these has got further SUB CATEGORY Rows which have yellow colors.

Now I want to create this list in such a way that when I add any NEW ROW to this list then it AUTOMATICALLY gets the correct number in the Column B, without needing me to enter it manually. Please have a look at the attached snapshot to understand it. I have also attached the excel sheet.

Auto-Numbering.png
Please suggest some way to do this.


I am using the following software versions -
Microsoft SQL Server Management Studio version-  12.0.2000.8,
Microsoft Office Professional Plus 2016 x64
and Windows 7 x64

Thanks a lot for any help.

Regards
Excel---Auto-Numbering.xlsx
happy 1001Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongSoftware Team LeadCommented:
try:

Private Const Color = 5287936
Private Const targetCol = 2

Sub putAutoNumber()
    Dim BigNo As Integer
    Dim SmallNo As Integer
    BigNo = 0
    SmallNo = 0
    lastRow = Range("A1").SpecialCells(xlCellTypeLastCell).Row
    For i = 2 To lastRow
        If Cells(i, 1).DisplayFormat.Interior.Color = Color Then
            BigNo = BigNo + 1
            SmallNo = 0
            Cells(i, targetCol) = BigNo
        Else
            SmallNo = SmallNo + 1
            Cells(i, targetCol) = BigNo & "." & SmallNo
        End If
    Next
End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ryan ChongSoftware Team LeadCommented:
rspahitzCommented:
This might work for you too, depending on what you want done.
Place it in the VBA code window for the sheet where you want it to run (e.g. Sheet1 (Sheet1))

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim row As Integer
    If Target.Address = Target.EntireRow.Address Then
        row = Target.row
        If Cells(row, 2) = "" Then
            Cells(row, 2).Value = Cells(row - 1, 2).Value + 0.1
        End If
    End If
End Sub

Open in new window

This will not renumber existing entries, and may not be what you want when it gets up past .9

Note that you may need to re-save your workbook as macro-enabled xlsm.
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Rgonzo1971Commented:
Hi,
if your criteria is the colour then
pls try

Function CountCcolor(range_data As Range, criteria As Range) As Long
    Dim datax As Range
    Dim xcolor As Long
xcolor = criteria.Interior.ColorIndex
For Each datax In range_data
    If datax.Interior.ColorIndex = xcolor Then
        CountCcolor = CountCcolor + 1
    End If
Next datax
End Function
Function IsSameCcolor(range_data As Range, criteria As Range) As Long
    Dim datax As Range
    Dim xcolor As Long
    xcolor = criteria.Interior.ColorIndex
    If range_data.Interior.ColorIndex = xcolor Then
        IsSameCcolor = True
    Else
        IsSameCcolor = False
    End If
End Function
Function CountSubCcolor(range_data As Range, criteria As Range) As Long
    Dim datax As Range
    Dim xcolor As Long
xcolor = criteria.Interior.ColorIndex
For Each datax In range_data
    If datax.Interior.ColorIndex = xcolor Then
        CountSubCcolor = CountSubCcolor + 1
    Else
        CountSubCcolor = 0
    End If
Next datax
End Function

Open in new window

Regards
Excel---Auto-NumberingV1.xlsm
Michelle GilbankMarketzone Database SpecialistCommented:
happy,
I believe the simplest way would be to simply copy an existing row, either yellow or green.
This can be done without creating a macro or adding code.

To do this, type the following in cell B2:
=IFERROR(INT(B1+1),1)
...and type the following in cell B3:
=B2+0.1

you can now right-click on either row 2 or 3, and copy it, and then paste it to the bottom of your list.

if you then copy cell B2 and paste into all of your existing green rows' B column cell, you can copy any existing green row to create a new row without needing to use row 2

if you then copy cell B3 and paste into all of your existing yellow rows' B column cell, you can copy any existing yellow row to create a new row without needing to use row 3

:-)
teslasmom
happy 1001Author Commented:
Thank you experts. :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.