Excel Numbering - Treat highlighted and non-highlighted rows differently.

Nick Wolf
Nick Wolf used Ask the Experts™
on
   I have an Excel (2007) spreadsheet with 8100 rows of data. Some rows are highlighted consecutively in pairs, threes, or greater numbers, the other rows are non-highlighted. I need help numbering these rows with a formula, macro, or however.

    I need for the spreadsheet numbering to start with a group of non-highlighted rows (ie. 1, 2, 3 ...), and when it gets to a group of highlighted rows (ie. 11, 12), treat the group of highlighted rows as a single row and number accordingly. (See the attached image for clarification).

    For example:
Number as 1---->  ROW 1 - [Text, no highlighting.]
Number as 2---->  ROW 2 - [Text, no highlighting.]
Number as 3---->  ROW 3 - [Text, no highlighting.]
Number as 4---->  ROW 4 - [Text, no highlighting.]
Number as 5---->  ROW 5 - [Text, no highlighting.]
Number as 6---->  ROW 6 - [Text, no highlighting.]
Number as 7---->  ROW 7 - [Text, no highlighting.]
Number as 8---->  ROW 8 - [Text, no highlighting.]
Number as 9---->  ROW 9 - [Text, no highlighting.]
Number as 10---->  ROW 10 - [Text, no highlighting.]
Number as 11---->  ROW 11 - [Text, HIGHLIGHTED.]
Number as 11---->  ROW 12 - [Text, HIGHLIGHTED.]
Number as 12---->  ROW 13 - [Text, no highlighting.]
Number as 13---->  ROW 14 - [Text, no highlighting.]
excel-numbering.png
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Kyle AbrahamsSenior .Net Developer

Commented:
Create a VBA macro

loop through the rows with a counter

if not row highlited
  counter += 1
end if

Assign counter to A <row>
next row.
Nick WolfEverything IT

Author

Commented:
@ged325: Thank you for your response, but I need some clarification please. I am not familiar with writing in VBA.

I open VBA Editor via Alt + F11 then Insert>Module.
I entered:
------------start here not including this line-------
if not row highlited
  counter += 1
end if

Assign counter to A <row>
next row.
------------end here not including this line-------
When I try to use this function in the workbook I get a VALUE error. I wasn't sure if "loop through the rows with a counter" was part of the macro so I tried with and without.  Thank you in advance for additional information/guidance.
Kyle AbrahamsSenior .Net Developer

Commented:
Hi Nick,

What I gave you was pseudo code . . . apologies thought you were a programmer.

Looping through rows is here:
http://support.microsoft.com/kb/299036

determining if cell is highlited here:
http://computerprogramming.suite101.com/article.cfm/changing_cell_color_w_vba_in_excel 
(see highlighting cell portion)

Assign cell value here:
http://p2p.wrox.com/excel-vba/17761-use-vba-assign-cell-values.html
But use Cells(1,rowNum).Value instead

That should get you started.  
Nick WolfEverything IT

Author

Commented:
Thanks again @ged325, but I think I am in waaay over my head.

This might make it simpler, I hope.

The script should check the background/highlighted color of each cell. If it is anything other than white (or even if it is white), print a value corresponding to the specific background color for that row. (For example, print the RGB value of the background color).

I'll try searching for this, but perhaps you or someone else can find it faster. Thank you so much in advance. This is a nightmare with 8100 rows of data to number.
I notice entire row is not highlighted. is column B alsways highlited?
Hi

I have uploade a spreadsheet which may be what you want.

If this is doing what you need an you need help transfering the vba to your workbook let me know and I will walk you through it.

Dont forget to enable macros.

Press Alt + F8 then run the macro called IndexMySheet

Good luck
HiLite.xlsm
You can also use a UDF. I am attaching below an example file and code below. To use this formula do the following:
1) Enter 1 in your top most cell (hard code 1)
2) From the 2nd cell onwards use the formula =fFormat() the way I have done in the attached example file.
3) Drag down as much as you want.
Let me know if you have any questions/concerns.
 
Shashank

Function fFormat(rng As Range)
strtarget = Application.Caller.Offset(-1, 0).Value
If (rng.Offset(-1, 0).Interior.ColorIndex = rng.Interior.ColorIndex) And (rng.Offset(-1, 0).Interior.ColorIndex <> xlNone) Then
fFormat = strtarget
Else
fFormat = strtarget + 1
End If
End Function

Open in new window

fFormat-example.xls

Commented:
You can use a macro to specify each cell's highlited status in a column (I used column J, you can change the "10" in the 5th row of code to a different number to specify that column (eg: 1 = A, 2 = B)
This macro will place the word TRUE in column J for any row that is highlited. Feel free to ask for a revision.

To make a macro, open the visual basic editor, click insert, module, and paste the code.
Then you can click macros, choose the macro (highlite), and click run.

Sub highlite()
Dim x As Integer
x = 1
Do Until Cells(x, 2).Value = ""
    If Cells(x, 2).Interior.ColorIndex <> -4142 Then Cells(x, 10).Value = "true"
    x = x + 1
Loop
End Sub
I think my solution should be the only one to be selected. Reasons being:
1) rowanscott's answer is close but not entirely correct. If you try to run the code/macro in the file which he has attached you will see that "c","d","e" are all getting an ID "3". Whereas as per the question "c" should have an ID 3 and "d" and "e" should have ID 4 and f should have ID 5 and so on.
2) etech0's answer is good but doesn't really matches what the user asked for.
My solution is entirely correct as well as flexible. User has the flexibility of choosing a column of his/her choice. I suggest that my anser is selected as the only correct solution.
I would appreciate if you can please tell me the reason to disagree with my comments above. My solution is entirely correct and both of the other two solutions are not (though they are good).
Nick WolfEverything IT

Author

Commented:
I sincerely apologize for the delay in responding to answers. I suppose lately I have become a bit jaded and am used to the notification emails from EE just being automated/administrative comments and not actual people with real solutions. :) (Nothing against EE, I love this site, I just seem to have some weird luck.)
Thank you so much. You rescued me. I was trying my best to convince the moderators here to choose my comment as the most appropriate solution but was having a hard time doing that. Thanks again :-)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial