Auto number column A while ignoring rows with bold text.

[asdsparky]

I need my spreadsheet to automatically insert a sequential number to all cells in column A (beginning with row 14) with the exception of the rows that contain bold text (header rows if you will, for each category). I also need to be able to add or delete rows and have the numbering auto correct the sequence. Does this sound do-able. The examples I keep finding are skipping hidden rows or nothing at all. I need something like this:

	Clipart Area
Column1	Column2	Column3	Column4	Column5	Column6
	Section 1 (bold text)
1	(THIS IS ROW 14) Non Bold Text Area	Non Bold Text Area			Non Bold Text Area
2	Non Bold Text Area	Non Bold Text Area			Non Bold Text Area
3	Non Bold Text Area	<v:shapetype id="_x0000_t75" stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600"> <v:stroke joinstyle="miter"> <v:formulas> <v:f eqn="if lineDrawn pixelLineWidth 0"> <v:f eqn="sum @0 1 0"> <v:f eqn="sum 0 0 @1"> <v:f eqn="prod @2 1 2"> <v:f eqn="prod @3 21600 pixelWidth"> <v:f eqn="prod @3 21600 pixelHeight"> <v:f eqn="sum @0 0 1"> <v:f eqn="prod @6 1 2"> <v:f eqn="prod @7 21600 pixelWidth"> <v:f eqn="sum @8 21600 0"> <v:f eqn="prod @7 21600 pixelHeight"> <v:f eqn="sum @10 21600 0"> </v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:formulas> <vath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"> <o:lock aspectratio="t" v:ext="edit"> </o:lock></vath></v:stroke></v:shapetype><v:shape id="TextBox_x0020_1" style="width: 0.375pt; height: 13.5pt; margin-top: -0.375pt; margin-left: 64.125pt; visibility: visible; position: absolute; z-index: 1;" type="#_x0000_t75" o:gfxdata="UEsDBBQABgAIAAAAIQC75UiUBQEAAB4CAAATAAAAW0NvbnRlbnRfVHlwZXNdLnhtbKSRvU7DMBSF dyTewfKKEqcMCKEmHfgZgaE8wMW+SSwc27JvS/v23KTJgkoXFsu+P+c7Ol5vDoMTe0zZBl/LVVlJ gV4HY31Xy4/tS3EvRSbwBlzwWMsjZrlprq/W22PELHjb51r2RPFBqax7HCCXIaLnThvSAMTP1KkI +gs6VLdVdad08ISeCho1ZLN+whZ2jsTzgcsnJwldluLxNDiyagkxOquB2Knae/OLUsyEkjenmdzb mG/YhlRnCWPnb8C898bRJGtQvEOiVxjYhtLOxs8AySiT4JuDystlVV4WPeM6tK3VaILeDZxIOSsu ti/jidNGNZ3/J08yC1dNv9v8AAAA//8DAFBLAwQUAAYACAAAACEArTA/8cEAAAAyAQAACwAAAF9y ZWxzLy5yZWxzhI/NCsIwEITvgu8Q9m7TehCRpr2I4FX0AdZk2wbbJGTj39ubi6AgeJtl2G9m6vYx jeJGka13CqqiBEFOe2Ndr+B03C3WIDihMzh6RwqexNA281l9oBFTfuLBBhaZ4ljBkFLYSMl6oAm5 8IFcdjofJ0z5jL0MqC/Yk1yW5UrGTwY0X0yxNwri3lQgjs+Qk/+zfddZTVuvrxO59CNCmoj3vCwj MfaUFOjRhrPHaN4Wv0VV5OYgm1p+LW1eAAAA//8DAFBLAwQUAAYACAAAACEA5Fn3hWYDAAB5DQAA HwAAAGNsaXBib2FyZC9kcmF3aW5ncy9kcmF3aW5nMS54bWzUl99P2zAQx98n7X+I/DqV/GjahoqA BqMICQ1E4Q8wjtNGc+zINqUF8b/vznFIV4Qmtqf2IXVsn/P93J3jy9HJuhbBimtTKZmT+CAiAZdM FZVc5OT+bjbISGAslQUVSvKcbLghJ8dfvxzR6ULTZlmxAFaQZkpzsrS2mYahYUteU3OgGi5hrFS6 phZu9SIsNH2ClWsRJlE0DmtaSXLcL/WDWho86uoflhKK/eLFGZUramBJwabbPV6jYP+/Mp3K1YVu 5s2NRuXs5+pGB1WRE/CcpDW4iIR+wE+D23DHatEvsC51jfNVWQbrnKSj5DAegtM3ORmmWRxHWbse X9uAwYTxiAQMBuNJkiQT/6zl9V+s2fL8Q3sQ14qAxpYw06AsuXpPmnSkdyDqVK2D2AURbq6MRSPU 6sL4Mpslp6PzWTqYQWuQRqfp4PQ8PRzMkmF2nkxmZ8lw/IrW8XjKNKcW0vCy6FIqHr+LV10xrYwq 7QFTdQhOqxjv0gqSKk7bpHIReYn8bwD/GV6i/hJBBuLvFV0I5KC5+3cU0OViizzQQC8Edg204Hpv 0vvG+Gz4/2C+BYNOG23sBVd1gI2caM6s8zNdgcBWdDfFRUrNKiHa/k6PXc9dFFF4scFZD/APJLDj 7TVcSqGecsJE1ZBgqfTzbt8T7PGcSNj6JBCX0rg0t11Dd42HrqGtOFPCzaKSwYo5aUWb5vujBYVe eCsDBQlj53YjuGuvRIx+rqm+cmtUsuAS2CMHXvDyFkbNM4Qgjto+o0RVIDjauzcPPxPAR0GEXXeR +mOWgCyTgd00vKQM9uu3Wg6E9buM7gxw6reY2RlgxmdBq8rljmj1e5SkR0lHE0g3EuwfD0J4nmHP cxin6X7yIITnSXueeDiJx/sJhBQeaLQFlCVZtp9ASOGBxj1QkmQQoL3cQkjhgSZbQJN0uKfvBKTw QFkPhDR7+lJACg90uAU0Hk329KWAFO2JtHW6ukqAy+KGaornqKBY43M5uJ9Djd8dqr6wcNZ92fBo +Ly5hQqkHe7qCvN2cstbXkLRBdWRP5eZXjzgSQwVAOxaeBBcH/DariDQAH1ewtn9SVtvgta8LEHU J+3fjNzzlezt60oqTbD7w1KibOc7/3h8qNmwWA53PjrcFP+RhF822/fHvwEAAP//AwBQSwMEFAAG AAgAAAAhAHM9h7YKBwAARiAAABoAAABjbGlwYm9hcmQvdGhlbWUvdGhlbWUxLnhtbOxZS4sbRxC+ B/IfhrnHq7e8i7Vm9fLaXu0uluzgY0tqzbTV86C7tWvdgn3KJRBwQi6B3HIIIYYYYnLJj1mwSZwf keqeV7fU8j4wwQm7gmWm5qvq6qqaqprqW7efBtQ5wYyTKGy55Rsl18HhJJqS0Gu5D0f9z266Dhco nCIahbjlLjF3b+9++skttDOhJB5HiE1HPg6wA4JCvoNari9EvLO1xSdARvxGFOMQns0iFiABt8zb mjJ0CgsEdKtSKjW2AkRCdxckCimoR+FfKLgkTCgbSjHYCVEAqx/NZmSCFXY6L0sEX/IOZc4Joi0X ZE6j0xF+KlyHIi7gQcstqT93a/fWFtpJmajYwKvx9dVfypcyTOcVtSbzxvmitVq91tjL5SsAFeu4 XrPX6DVyeQqAJhPYaaKLKbNZ6dRSrAZKLi2yu81utWzgNfnVNZ336vJn4BUokV9bw/f7HbCigVeg BF9fw9fb2+2uKV+BEnxjDd8s7XVrTUO+AvmUhPM1dKneqHay3eaQWUT3rfDteq3frKTCCxREQx5d colZFIpNsRagJxHrA0ACKRIkdMQyxjM0gZjsIErGjDgHxPMh8GIURhzIpUqpX6rCf/mrqSvlUbSD kcYt9QJN+BpJ6uPwCSOxaLn3QKqrQd68fn327NXZs9/Onj8/e/ZLurYSZfDto9DT+d79+PXf33/h /PXrD+9efJMsvYrnOv7tz1++/f2P94mHHRemePPty7evXr757qs/f3phkb7H0FiHj0iAuXOIT50H UQAbtOiPx+xyHCMfEYMD+SDbIronfAN4uETUhmtj04SPGGQZG/DO4omh69BnC0EsK9/3AwM4iCLa jpjVAPflWpqFR4vQsy/OFjruAUIntrU7KDQc3FvEkF6JTWTHx4aaxxSFAnk4xMKRz6I5xpbdPSbE sOuATFjEo5lwHhOnjYjVJCMyNgKpYNonAfhlaVMQXG3YZvDIaUfUtusuPjGR8FogalF+hKlhxjto IVBgEzlCAdUNfoCEb1NyuGQTHdfjAjztYRo5vSnm3MZzxGC/mtPvQ4axu31Al4GJZILMbTIPUBTp yG407/goiG3YIQl9HXuXzyFEkXMcCRt8EJlviLwHP6Bwo7sfEWy4+/xE8BCSq65SESDyyYJZfHkH R0b8Dpd0hrAty+yxwMiue4xYo6O98IzQPsCYolM0xdh5eNeiQTuKDZsXSt/zIavsY1tg3UNmrMr7 EHPsqL5mPUUeEG6E7BB70QZ9BsuVxLNEYYDYJsmH4HXd5j2octZUekQncx14SKDVg3ixGuWIgwwt uDdKPfaRUbvkPbfH65IZ/rvIOwbv5RNDjQu8l8CDL80DiV3nea9tRogaCxQBM0LQYNjSLbAY7i9Y ZF1VbAsr38x8aQs3QGNk9DsBCc9tflbanvq/0/ZYXrkP0/DYBRsp65KtzqaUsr/S4GzC/Qfbmi5a hMcYKsl6zrruaq67Gvd/39Vsepeve5lNHcd1L+NCj3Hdy6STlQ/TyxTtC3Q2ctqRTHnUzCfYOPKZ EUqHYknxAVdTHw5fNNM+ECWfGm3ifAQY+3ApyxwsYOA8hhSPwyLxORH+0EcxjIbKrhTi8VS0x504 4jAxUmSrbImni2AQTZNJZ7ksp5pJZeVIFPRSPafDlEok6EazmN7l4pW2npqyZgpI3ssooS1mKlG1 KNHMiNJIaqYLRrMooXb2QbTYtmhxU4rPXLWmBaiWewU+uR34UG+59RqwABMM46A9n0o/Ja7OvKuc +SE9vcmYRgRAi51FQOHpbanrxu3J3SWhdgFPG0po4WYqoSyjGjzuw4dwGp2SehE1Luvr7cKlhnrS FGo9CK1CjebN92lxVV8D32puoKGeKWjonLbcRrUOITNBccudwcQYLoMYYofLry5EPThrmQiWvPBX ySwx46KLuJ8YXCWdJBsERGDmUBK0XLn93A00VDlE6VauQEL4aJXbhrTysSkHTjedjGczPBG62zWK tHRyCxk+yRXWp4r96mDJGS3A3UN/euqM6YI9QBBi9WZZGnBKOBwclBNrTgmchOWJrIi/lcKUpl39 KErFUEJHNPZRWlH0ZJ7AVSrP1VF3uQ20u3TPYFDNJGkhHHuywOpGNappXjUSHTZW3fOZpOW0pFnU TCOryKppz2LGClkZWLHl1Yq8plVmYshpeoVPUvdqyt3Oct1Kn5BXCTB4bj9L1b1AQdBUKxYzVJMa r6dhmbNTqlk7sg2eo9pFioSW9RuZ2BW75TXCuhwQr1T5gW81aoE0y/pKZWnbqfYAxc7YK7dcOFmG 8eBTuIKzaRdoFUmrSBpcwYEzlIvklLjlphcZBZ4nlBxTzSjVDFPLKLWMUs8o9YzSyCgN11HHqXCE L09SXSc7LYUalp6upr2FefS/+w8AAAD//wMAUEsDBBQABgAIAAAAIQCcZkZBuwAAACQBAAAqAAAA Y2xpcGJvYXJkL2RyYXdpbmdzL19yZWxzL2RyYXdpbmcxLnhtbC5yZWxzhI/NCsIwEITvgu8Q9m7S ehCRJr2I0KvUBwjJNi02PyRR7Nsb6EVB8LIws+w3s037sjN5YkyTdxxqWgFBp7yenOFw6y+7I5CU pdNy9g45LJigFdtNc8VZ5nKUxikkUigucRhzDifGkhrRykR9QFc2g49W5iKjYUGquzTI9lV1YPGT AeKLSTrNIXa6BtIvoST/Z/thmBSevXpYdPlHBMulFxagjAYzB0pXZ501LV2BiYZ9/SbeAAAA//8D AFBLAQItABQABgAIAAAAIQC75UiUBQEAAB4CAAATAAAAAAAAAAAAAAAAAAAAAABbQ29udGVudF9U eXBlc10ueG1sUEsBAi0AFAAGAAgAAAAhAK0wP/HBAAAAMgEAAAsAAAAAAAAAAAAAAAAANgEAAF9y ZWxzLy5yZWxzUEsBAi0AFAAGAAgAAAAhAORZ94VmAwAAeQ0AAB8AAAAAAAAAAAAAAAAAIAIAAGNs aXBib2FyZC9kcmF3aW5ncy9kcmF3aW5nMS54bWxQSwECLQAUAAYACAAAACEAcz2HtgoHAABGIAAA GgAAAAAAAAAAAAAAAADDBQAAY2xpcGJvYXJkL3RoZW1lL3RoZW1lMS54bWxQSwECLQAUAAYACAAA ACEAnGZGQbsAAAAkAQAAKgAAAAAAAAAAAAAAAAAFDQAAY2xpcGJvYXJkL2RyYXdpbmdzL19yZWxz L2RyYXdpbmcxLnhtbC5yZWxzUEsFBgAAAAAFAAUAZwEAAAgOAAAAAA== " o:spid="_x0000_s1033"> <v:imagedata o:title="" src="file:///C:/Users/davisa/AppData/Local/Temp/msohtmlclip1/01/clip_image001.png"> <o:lock aspectratio="f" v:ext="edit"> <x:clientdata ObjectType="Pict"> <x:sizewithcells> <x:cf>Bitmap</x:cf> <x:autopict> </x:autopict></x:sizewithcells></x:clientdata> </o:lock></v:imagedata></v:shape> Non Bold Text Area <tbody> </tbody>			Non Bold Text Area
4	Non Bold Text Area	Non Bold Text Area			Non Bold Text Area
	Section 2 (bold text)
	Subsection 2a (bold text)
5	Non Bold Text Area	Non Bold Text Area			Non Bold Text Area
6	Non Bold Text Area	Non Bold Text Area			Non Bold Text Area
7	Non Bold Text Area	Non Bold Text Area			Non Bold Text Area
8	Non Bold Text Area	Non Bold Text Area			Non Bold Text Area
9	Non Bold Text Area	Non Bold Text Area			Non Bold Text Area
10	Non Bold Text Area	Non Bold Text Area			Non Bold Text Area
	Section 3 (bold text)
	Subsection 3a (bold text)
11	Non Bold Text Area	Non Bold Text Area			Non Bold Text Area
12	Non Bold Text Area	Non Bold Text Area			Non Bold Text Area
13	Non Bold Text Area	Non Bold Text Area			Non Bold Text Area
14	Non Bold Text Area	Non Bold Text Area			Non Bold Text Area
15	Non Bold Text Area	Non Bold Text Area			Non Bold Text Area
16	Non Bold Text Area	Non Bold Text Area			Non Bold Text Area
17	Non Bold Text Area	Non Bold Text Area			Non Bold Text Area

<tbody>
</tbody>

 

[Scott T]

Try this code. In Excel right click on the tab you want this code to run on and select view code. Past the code below in the VBA editor. When you make a change to column B the code will run, NOTE that changing a cell in column B from bold to not bold or vice versa does not trigger the code as excel does not see it as a change in value. If the cell in column B is blank but the cell is formatted as bold it will not be numbered and if it is not it will be numbered. To make sure the numbers are right the code will delete the range A14 to last row used in column A.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim lr As Long
Dim lra As Long
Dim rnum As Long
lr = Cells(Rows.Count, 2).End(xlUp).Row
lra = Cells(Rows.Count, 1).End(xlUp).Row
rnum = 1
Stop
If Not Intersect(Target, Columns(2)) Is Nothing Then
Application.EnableEvents = False
Range("A14:A" & lra).ClearContents
    For x = 14 To lr
        If Cells(x, 2).Font.Bold = False Then
            Cells(x, 1) = rnum
            rnum = rnum + 1
        End If
    Next x
    Application.EnableEvents = True
End If
End Sub

 

[asdsparky]

It's not identifying a specific error but when I apply the code and make a change to column B, the VBA pops up and highlights the line "Stop" in the code.

*Update: I took "stop" out of the code and everything works perfectly. Not sure why you had it in there but it works now. Thanks.

 

[Scott T]

Sorry put the stop in for testing and forgot to take it out.