Excel Formating Help (Excel 2000)

Topic

New Reply

I have a list of part numbers with rows of information about each. The part number exist in the same column on each row. There are from one to eight rows for each part number. The question is if it is possible to use conditional formatting to insert a blank row at each change in part number or any other means to separate for easy reading. Could be done with colors if inserting a row is not possible. Thank-you for any help you might provide.

Reply | Quote

Replies

ASAP-Utilitites (http://www.asap-utilities.com/) is the BEST and free download that can do this for you as well as a zillion other cool tricks. What you’re looking for can be found in the Columns/Rows menu. Do a conditional select, and fill with the color of your choice. You might even be able to insert empty rows…I got a runtime error when I tried it.

Reply | Quote

Conditional formatting can not insert blank rows, you would need a fairly simple VBA macro to do that. If the part numbers are in column A of the Active Sheet, then the following macros will insert a blank row at each part number change:

Option Explicit
Public Sub InsertBlankRows()
Dim I As Long
    With ActiveSheet
        For I = .Range("A65536").End(xlUp).Row - 1 To 1 Step -1
            If .Range("A1").Offset(I, 0).Value  .Range("A1").Offset(I - 1, 0).Value Then
                .Range("A1").Offset(I, 0).EntireRow.Insert
            End If
        Next I
    End With
End Sub

You could use conditional formatting to change the color of the first row or last row of each group a different color, or put a colored line border between groups, or any other formatting that can be done with conditional formatting. If you want to do this, then tell us what column the part numbers are in, what formatting you would prefer, and if you want it on the first or last row of each group.

Reply | Quote

Conditional formatting can not insert blank rows, you would need a fairly simple VBA macro to do that. If the part numbers are in column A of the Active Sheet, then the following macros will insert a blank row at each part number change:

Option Explicit
Public Sub InsertBlankRows()
Dim I As Long
    With ActiveSheet
        For I = .Range("A65536").End(xlUp).Row - 1 To 1 Step -1
            If .Range("A1").Offset(I, 0).Value  .Range("A1").Offset(I - 1, 0).Value Then
                .Range("A1").Offset(I, 0).EntireRow.Insert
            End If
        Next I
    End With
End Sub

You could use conditional formatting to change the color of the first row or last row of each group a different color, or put a colored line border between groups, or any other formatting that can be done with conditional formatting. If you want to do this, then tell us what column the part numbers are in, what formatting you would prefer, and if you want it on the first or last row of each group.

Reply | Quote

John,

Another alternative …
My “Shade Data Rows” Excel add-in colors identical adjacent rows in alternating colors.
I’ve attached the one page word.doc use/install instructions.
The program is available (free) upon direct request : jim.coneXXX@rcn.comXXX (remove XXX)

Regards,
Jim Cone
San Francisco, CA

Reply | Quote

John,

Another alternative …
My “Shade Data Rows” Excel add-in colors identical adjacent rows in alternating colors.
I’ve attached the one page word.doc use/install instructions.
The program is available (free) upon direct request : jim.coneXXX@rcn.comXXX (remove XXX)

Regards,
Jim Cone
San Francisco, CA

Reply | Quote