can this be done with conditional formatting

[rodchar]

hey all

i have a single column like the following:

value1
value1
value2
value2
value3
value3

is there a way to do alternating shading of the row based on when the value
changes?

thanks,
rodchar

 

[JBeaucaire]

Not that I know of. Here's a macro that will do it, just run it on-demand to
reset the "banding" of colors based on the values in column A:

Sub RowBanding()
Dim rng As Range, lastrow As Long, cell As Range, i As Variant
Dim Color1 As Integer, Color2 As Integer

lastrow = ActiveSheet.UsedRange.Rows.Count
lastcol = ActiveSheet.UsedRange.Columns.Count

Set rng = Range("A2:A" & lastrow)

Color1 = 6
Color2 = 37
i = Color1

Range(Cells(1, 1), Cells(1, lastcol)).Interior.ColorIndex = i

For Each cell In rng
If cell.Value = cell.Offset(-1, 0).Value Then
Range(Cells(cell.Row, 1), Cells(cell.Row, lastcol)). _
Interior.ColorIndex = cell.Offset(-1, 0).Interior.ColorIndex
Else
If i = Color1 Then
i = Color2
Range(Cells(cell.Row, 1), Cells(cell.Row, lastcol)) _
..Interior.ColorIndex = i
Else
i = Color1
Range(Cells(cell.Row, 1), Cells(cell.Row, lastcol)) _
..Interior.ColorIndex = i
End If
End If
Next cell
End Sub

 

[Elkar]

This would probably be easiest to do with a helper column. Let's say your
data is in Column A. In cell B1 enter a 1, then in cell B2 enter the formula:

=IF(A2=A1,B1,-B1)

Copy this formula down column B as far as needed.

Then, highlight Column A, select Conditional Formatting, and use this formula:

=B1=1

Then select your format.

You could also set a second condition to:

=B1=-1

Then select a different color.

You can then hide your helper column to get it out of the way. That should
do it.

HTH
Elkar

 

[rodchar]

thanks for the help everyone,
rod.