Keeping cell colors when sorting

[automatic]

I have a sheet that has every other row white and yellow so that each row is easier to read. I have a macro assigned to sort certain columns within the table. Is there a way that I can sort these categories while keeping the rows the same color. Now when I do it, I will get three white rows and then two yellow, etc. I want to keep every other row a different color. Thanks.

[mudraker]

have your sort macro call this macro after it has sorted your data by adding the command.

Call ColourRows command before the End Sub of your sort macro.

Change ColorIndex = 36 number to suit your needs


Sub ColourRows()
Dim LastRow As Long
Dim lRow As Long

LastRow = Cells.Find(what:="*", searchorder:=xlByRows, _
searchdirection:=xlPrevious).Row

For lRow = 2 To LastRow Step 2
Rows(lRow).Interior.ColorIndex = 36
Rows(lRow + 1).Interior.ColorIndex = xlNone
Next lRow
End Sub

[automatic]

Your suggestion was very helpful. However, it colors the whole row. I would like it to only turn the cells that I have already selected. I have attached an example of what I am talking about. I have turned the cells yellow and white like I want them. I have also created the ColorRows macro like you said before. Thanks for the help.

[mudraker]

Simple enought to fix.

Macro now works only on column C & D
I have left 2 non active lines of code in the macro in case you need to run macro on column F as well.
To activae remove the ' at the start of the line of code

Please Login or Register  to view this content.

[automatic]

The recent code works perfectly. Is there a shorter way to write the code if I have to sort more than three columns. I have to sort about 30 columns, so it takes a little while to enter all the column letters in the formula that you gave me. Thanks for the help so far. It does exactly what I want.

[mudraker]

Not if the various columns are seperate from each other and you don't want the columns in between coloured or existing colours removed.

For columns next to eachother just change the "c" & "d" in this line of code to suit

Range("C" & lRow & ":d" & lRow).Interior.ColorIndex = 19