Hello,
I am working in Excel 2013 and trying to color rows of a used range automatically after column G (US column) or I (DS column) is sorted. Another user will be having to sort (sort either desc or asc, it will vary) between these 2 columns over and over and I need to rows to be colored automatically after each sort. They don't want to push a button to color the rows after each sort.
tonyyy helped me some weeks ago do something similar with the code I have below. He added the parts I've designated '<==HERE' to color rows after a button was pushed to hide & unhide rows that contained certain text in a certain column (I've removed the button code to hide and unhide).
I'm sure all I need to do is replace his code where it says '<==HERE' with code to test as follows:
, and then proceed with the code to color the rows.
Problem:
I just don't know how to test if a column is sorted.
I've seen some posts using a formula and one using a loop to go through each cell to see if it is sorted comparing to the cell above, but the sort we have will vary between ascending and descending in either columns G and I. Also, I didn't know if those are efficient ways to do it or if there is another way.
FYI:
Columns G and I are numerical values but I don't remember if they export as text. This data is exported from the web to Excel. (The example wkbook via this link on Google Drive shows the columns G and I as text with the code below in the Color_BySort module: Color based on Sort.xlsm - Google Drive )
I would appreciate any help, even if you could lead me in the right direction. Thank you in advance.
I am working in Excel 2013 and trying to color rows of a used range automatically after column G (US column) or I (DS column) is sorted. Another user will be having to sort (sort either desc or asc, it will vary) between these 2 columns over and over and I need to rows to be colored automatically after each sort. They don't want to push a button to color the rows after each sort.
tonyyy helped me some weeks ago do something similar with the code I have below. He added the parts I've designated '<==HERE' to color rows after a button was pushed to hide & unhide rows that contained certain text in a certain column (I've removed the button code to hide and unhide).
I'm sure all I need to do is replace his code where it says '<==HERE' with code to test as follows:
Code:
If column G is sorted or column I is sorted ThenProblem:
I just don't know how to test if a column is sorted.
I've seen some posts using a formula and one using a loop to go through each cell to see if it is sorted comparing to the cell above, but the sort we have will vary between ascending and descending in either columns G and I. Also, I didn't know if those are efficient ways to do it or if there is another way.
FYI:
Columns G and I are numerical values but I don't remember if they export as text. This data is exported from the web to Excel. (The example wkbook via this link on Google Drive shows the columns G and I as text with the code below in the Color_BySort module: Color based on Sort.xlsm - Google Drive )
I would appreciate any help, even if you could lead me in the right direction. Thank you in advance.
Code:
Sub ColorBySort()
'Color report based on Sort by US column (G) or DS Column (I)
'NOT FINISHED
Application.ScreenUpdating = False
Dim colorrng As Range
Dim rng As Range
Dim r As Long
Dim lastrow As Long
Dim colourIt As Boolean
Dim colour As XlColorIndex
'*******Set colourit variable for Color Rows code***********
lastrow = Cells(Rows.Count, "B").End(xlUp).Row
colourIt = False
Set colorrng = Range("A1:L" & lastrow).SpecialCells(xlCellTypeVisible)
'*******Set rng variable for sort Rows code***********
Set rng = Range("G2:G" & lastrow)
'*******COLOR ROWS CODE***********
With ActiveSheet
r = 2 ' First row of data on row 2
'clears any coloring from before
Range("A2:M" & lastrow).Interior.Color = xlColorIndexNone
Do While .Cells(r, "B").Value <> ""
'See if value has changed - highlight by Service within each hub - "B" is Service column
'Must arrange data so that "B" is Svc Group column
If Rows(r).Hidden = False Then '[B]<====HERE[/B]
If .Cells(r, "B").Value <> .Cells(r - 1, "B").Value Then
colourIt = Not colourIt
End If
End If
'Determine which colour to use on this row
If colourIt Then
colour = RGB(217, 225, 242)
Else
colour = xlColorIndexNone
End If
'Apply the colouring - starting in row r = 2 and column "A"
If Rows(r).Hidden = False Then '[B]<====HERE[/B]
.Range(.Cells(r, "A"), .Cells(r, .Cells(r, .Columns.Count).End(xlToLeft).Column)).SpecialCells(xlCellTypeVisible).Interior.Color = colour
'colorrng.Rows(r).Interior.Color = colour
End If
'Point to the next row of data
r = r + 1
Loop
End With
Application.ScreenUpdating = True
On Error GoTo 0
End Sub
Last edited:
