Excel autonumber in column 1 based on level of indent in column 2

I have a spreadsheet with Column 2 containing tasks for a project. In column one, I would like to have an autonumber feature, (1,2,3,3.1,3.2,3.3,4.11,4.12, etc) which does the following:

1. Determine the level of indent on the cell adjacent right (column 2)
2. Increment based upon the cell above or if indent is "out-dented" incement from last indent of same level

Does this make sense? I am not sure if this is easy or massively difficult. Help me understand and I will go away if this is too hard.

A sample with a column 2 indents is attached.

Thank you!
John DarbyPMAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:

sounds like wrong tool for the job. Why don't you use Word instead? It's built-in and ready to go.

cheers, teylyn
Martin LissOlder than dirtCommented:
I'll modify the workbook I created for you.
Martin LissOlder than dirtCommented:
You'll need to format column A as Text to get it to align.

Sub ColorByIndentLevel()
Dim lngLastRow As Long
Dim lngRow As Long
lngLastRow = Range("B1048576").End(xlUp).Row
Dim intIndent0 As Integer
Dim intIndent1 As Integer
Dim intIndent2 As Integer
Dim intIndent3 As Integer
Dim intIndent4 As Integer

For lngRow = 3 To lngLastRow ' To start with row 1, change the 3 to a 1)
    If Cells(lngRow, 2).Text <> "" Then
        With Cells(lngRow, 2)
            ' Other than black (case 0), the bigger the number (up to 255
            ' which is white) the lighter the shade of gray
            Select Case .IndentLevel
                Case 0
                    .Interior.Color = RGB(0, 0, 0)
                    .Font.Color = vbWhite
                    intIndent0 = intIndent0 + 1
                    Cells(lngRow, 1) = intIndent0
                    intIndent1 = 0
                    intIndent2 = 0
                    intIndent3 = 0
                    intIndent4 = 0
                Case 1
                    .Interior.Color = RGB(128, 128, 128)
                    intIndent1 = intIndent1 + 1
                    Cells(lngRow, 1) = intIndent0 & "." & intIndent1
                    intIndent2 = 0
                    intIndent3 = 0
                    intIndent4 = 0
                Case 2
                    .Interior.Color = RGB(169, 169, 169)
                    intIndent2 = intIndent2 + 1
                    Cells(lngRow, 1) = intIndent0 & "." & intIndent1 & "." & intIndent2
                    intIndent3 = 0
                    intIndent4 = 0
                Case 3
                    .Interior.Color = RGB(211, 211, 211)
                    intIndent3 = intIndent3 + 1
                    Cells(lngRow, 1) = intIndent0 & "." & intIndent1 & "." & intIndent2 & "." & intIndent3
                    intIndent4 = 0
                Case 4
                    .Interior.Color = RGB(220, 220, 220)
                    intIndent4 = intIndent4 + 1
                    Cells(lngRow, 1) = intIndent0 & "." & intIndent1 & "." & intIndent2 & "." & intIndent3 & "." & intIndent4
                Case Else
                    MsgBox "Add code for Case 5"
            End Select
        End With
    End If

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
Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

John DarbyPMAuthor Commented:
Dang Martin, that rocks! It also is teaching me how to do it, since I have 3 more levels of indent to create (Cases 5-7).

Thank you Professor! :)
John DarbyPMAuthor Commented:
Marty is the master of Excel and VBA! Thank you, my friend!
Martin LissOlder than dirtCommented:
Thank you for the kind words and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2014
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.