Format->Colorize does not work with data away from row 1
byronwall opened this issue · 3 comments
byronwall commented
Code is using a bare call to Rows
to determine which rows to color. Should probably consider the rows inside of the selected Range
instead of the Worksheet
at large.
RaymondWise commented
Changing the macro like this will base the colorizing on the selected range, regardless of where it starts. It will also only color the cells within the selected range. If that's the issue?
Public Sub Colorize()
Dim rngToColor As Range
On Error GoTo errHandler
Set rngToColor = Application.InputBox("Select range to color", Type:=8)
Dim lastrow As Integer
lastrow = rngToColor.rows.count
likevalues = MsgBox("Do you want to keep duplicate values the same color?", vbYesNo)
If likevalues = vbNo Then
For i = 1 To lastrow
If i Mod 2 = 0 Then
rngToColor.rows(i).Interior.Color = RGB(200, 200, 200)
Else: rngToColor.rows(i).Interior.ColorIndex = xlNone
End If
Next
End If
If likevalues = vbYes Then
Dim flip As Boolean
For i = 2 To lastrow
If rngToColor.Cells(i, 1) <> rngToColor.Cells(i - 1, 1) Then
flip = Not flip
End If
If flip Then
rngToColor.rows(i).Interior.Color = RGB(200, 200, 200)
Else: rngToColor.rows(i).Interior.ColorIndex = xlNone
End If
Next
End If
Exit Sub
errHandler:
MsgBox ("No Range Selected!")
End Sub
byronwall commented
Yes, this was exactly what I was referring to. I figured the easy fix was to add the rngToColor
before the Rows
. Glad it was that simple. I'll test this out and confirm it's resolved.
byronwall commented
Works nicely. Thanks for knocking this out, @RaymondWise .