I recently worked on a project where there was the need to sort on columns and band the rows based on the data in the table. That is to say, color the rows in alternate colors based on the data in each sorted column. The issue is that there is not a set pattern to the rows. This makes the color banding irregular.
.
I came up with a nifty macro that automatically sorts and bands the rows each time a user double-clicks a column. Both the sorting and color banding is based on the column that is double-clicked. There probably is a more elegant way to apply the color banding, but this works for me.
.
.
To do the same with your data, simply copy and paste this code into the Before_Doubleclick event of the worksheet.
.
Visual Basic:
-
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
-
Dim LastRow As Long
-
Dim MyCell As Range
-
Dim CurrentColor As Integer
-
-
Const WHITE As Integer = 2
-
Const GREEN As Integer = 35
-
-
'Find last row
-
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
-
-
'Sort ascending on double-click column
-
Rows("2:" & LastRow).Sort _
-
Key1:=Cells(2, ActiveCell.Column), _
-
Order1:=xlAscending
-
-
'Start Color
-
CurrentColor = GREEN
-
-
'Loop through cells in double-clicked column
-
For Each MyCell In Range(Cells(2, ActiveCell.Column), Cells(LastRow, ActiveCell.Column)).Cells
-
-
'Check to see if value is different from previous cell
-
If MyCell = MyCell.Offset(-1, 0) Then
-
-
'If same, then use same color
-
MyCell.EntireRow.Interior.ColorIndex = CurrentColor
-
Else
-
-
'If different, then determine alternate color
-
Select Case CurrentColor
-
Case Is = GREEN
-
CurrentColor = WHITE
-
Case Else
-
CurrentColor = GREEN
-
End Select
-
-
'Apply alternate color
-
MyCell.EntireRow.Interior.ColorIndex = CurrentColor
-
End If
-
Next MyCell
-
-
End Sub
.
Notes:
- Be aware that the code assumes that column headers start on A1.
- As written, the code works best where the data is a contiguous range with nothing else on the sheet. You will have to adjust the code if this is not the case in your situation.
- The color banding will not overtake any Conditional Formatting, but it will completely blow away any cell coloring you have on your sheet.
- You can download the sample file here.
.
Enjoy!
Assuming you have Description starting from C2:C20
The alternate irregular banding can be achieved via Conditional Formatting using the below formula
=ROUND(MOD(SUM(1/COUNTIF(C2:C$20,C2:C$20)),2),0)=1 - Apply Light Green Format
For Sorting we could apply a filter click on the drop down a say Sort A to Z
For making it Dynamic you could define it as a List(2003) or a Table (2007/2010)
VBA ???
Here is code I have used in the past to do this (I commented it for presentation here)... it uses a For..Next rather than a For Each..Next loop and uses a different method to swap the colors back and forth. Since a user can only double click a single cell, I chose to use Target rather than ActiveCell when determining the column to sort on. Unlike your code, my code tests to make sure the code only reacted to a double-click on the header row and, because of this restriction, I use Cancel=True to stop that action from entering edit mode. Okay, here is the code...
Follow up question... how come the code you posted in your blog article has large colored text, is nicely indented and its window is horizontally scrollable whereas the code I posted using
tags is none of those things. How can we get our code to look like the code you posted (obviouslytags are not the way)?Rick: it looks like the only tags that will create the nice codeframe is the vb /vb tags (in brackets)
I applied them to your code.
Thanks for converting the code for me (looks like the indenting got kind of messed up though). There is no "Plain Text" tags listed in your tag list... is that a set of tags a commenter can apply? Also, I think you used the actual tags (angle brackets and all) in your reply as they got converted to an empty "Plain Text" box. Can you post the tags without the angle brackets so the next person to post code can try it and see if it works or not?
Rick Rothstein:
I do like the extra line limiting the double-click to the header row.
datapig: I liked the code except that I dislike the entire row being highlighted. Not being an MVP, (so excuse the code if it is not optimum) I coded the following to limit the row highlighting: