can this be done with conditional formatting
 

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

can this be done with conditional formatting
 

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



--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"rodchar" wrote:

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

can this be done with conditional formatting
 

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" wrote:

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

can this be done with conditional formatting
 

thanks for the help everyone,
rod.

"rodchar" wrote:

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