Link to home
Start Free TrialLog in
Avatar of Nick Wolf
Nick WolfFlag for United States of America

asked on

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

   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
Avatar of Kyle Abrahams, PMP
Kyle Abrahams, PMP
Flag of United States of America image

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.
Avatar of Nick Wolf

ASKER

@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.
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.  
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
ASKER CERTIFIED SOLUTION
Avatar of sstampf
sstampf
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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).
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 :-)