Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Aug 2001
    Location
    Bloomington, Indiana, USA
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Good day Loungers!
    I have a bit of a puzzle that I want to pose.

    I have a workbook that contains employee ID #'s and other info about that employee. Each employee may have 1 or more rows. I would like to change the shading of the row each time the employee # changes.

    For example, employee #1 has 1 row so I want that row to remain unshaded. Employee 2 has 5 rows and I want all 5 of those rows to be shaded, Emplyoee 3 has 2 rows and they should be unshaded etc...

    I have an add-in (ShadeDataRows.xla) that I downloaded that works, but I really want to incorporate this process into another macro that I am running on the same data.

    I have attached a sample workbook that shows the results as I want them .

    TIA,
    Greg
    Attached Files Attached Files
    Greg <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Try this:
    Code:
    Sub shading()
    Dim i As Long, lrow As Long, iShade As Integer
    Dim bolShade As Boolean
    
    bolShade = True
    lrow = Cells(Rows.Count, 1).End(xlUp).Row
    For i = 2 To lrow
    	If Cells(i, 1) <> Cells(i - 1, 1) Then
     	bolShade = Not bolShade
     	If bolShade = False Then
     	With Range("A" & i & ":E" & i)
     	.Interior.ColorIndex = 15
     	End With
     	Else
     	With Range("A" & i & ":E" & i)
     	.Interior.ColorIndex = -4142
     	End With
     	End If
    	Else
     	With Range("A" & i & ":E" & i)
     	.Interior.ColorIndex = Cells(i - 1, 1).Interior.ColorIndex
     	End With
    	End If
    Next
    End Sub

  3. #3
    Star Lounger
    Join Date
    Aug 2001
    Location
    Bloomington, Indiana, USA
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts
    This works great. Thank you!

    Is there any way to make this adaptable for the ending column? The last column is not always E, but could be different for the rows. The sample I sent had most of the coulmns trimmed out to remove sensitive data.

    tia,
    Greg
    Greg <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

  4. #4
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    This should do it

    Code:
    Sub shading()
    Dim i As Long, lrow As Long, iShade As Integer, lCol As Long
    Dim bolShade As Boolean
    
    bolShade = True
    lCol = Cells(1, Columns.Count).End(xlToLeft).Column
    lrow = Cells(Rows.Count, 1).End(xlUp).Row
    For i = 2 To lrow
    	If Cells(i, 1) <> Cells(i - 1, 1) Then
     	bolShade = Not bolShade
     	If bolShade = False Then
     	With Range(Cells(i, 1), Cells(i, lCol))
     	.Interior.ColorIndex = 15
     	End With
     	Else
     	With Range(Cells(i, 1), Cells(i, lCol))
     	.Interior.ColorIndex = -4142
     	End With
     	End If
    	Else
     	With Range(Cells(i, 1), Cells(i, lCol))
     	.Interior.ColorIndex = Cells(i - 1, 1).Interior.ColorIndex
     	End With
    	End If
    Next
    End Sub

  5. #5
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    Here is a variant using conditional formatting.[attachment=86837:Custom Formatting for Rebecca.xls]
    Attached Files Attached Files
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  6. #6
    Star Lounger
    Join Date
    Aug 2001
    Location
    Bloomington, Indiana, USA
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Perfect!
    Thanks
    Greg <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

  7. #7
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The conditional formatting method will work if there are blanks between the ID#s. The sheet in question has repeating values in the ID# field. Using the CF method with gvanhook's sheet will result in alternating rows being shaded.

  8. #8
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    [attachment=86855:counting variable numbers of records 2.xlsx]
    I meant that it could be used as a jumping off point if one wanted a Conditional Formatting solution.
    Here is a CF solution. I admit it would be more elegant if one could avoid adding the additional columns used. However, the record count field is often quite useful and not only for formatting.
    Attached Files Attached Files
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •