Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formatting Conditional Formatting Icon Sets | Excel Discussion (Misc queries) | |||
Formatting cells in a column with conditional formatting? | Excel Discussion (Misc queries) | |||
Protect Cell Formatting including Conditional Formatting | Excel Discussion (Misc queries) | |||
conditional Formatting based on cell formatting | Excel Worksheet Functions | |||
Conditional Formatting that will display conditional data | Excel Worksheet Functions |