Row Format by Group
 

I have a spreadsheet where column A contains a Product Group and it is sorted
in this order. Each Product Group may be on several Rows. I'd like to
change the colour of the row when the Product Group changes value. i.e.
similar to formatting alternate Rows but based on the change in value in
Column A...How?

Row Format by Group
 

Hi Charlie

FormatConditional FormattingFormula is=$A2<$A1Formatchoose what you
want

--
Regards
Roger Govier

"Charlie Bamford" wrote in
message ...
I have a spreadsheet where column A contains a Product Group and it is
sorted
in this order. Each Product Group may be on several Rows. I'd like to
change the colour of the row when the Product Group changes value. i.e.
similar to formatting alternate Rows but based on the change in value in
Column A...How?

Row Format by Group
 

Thanks Roger

This highlights the last row for each Group which helps but it doesn't go
quite as far as I would like. For example, all rows with Group 14DS should
be filled with yellow. The next 6 rows have Group 18AL. No fill is required
for these wows. After these comes Group 18BB and the fill should revert to
yellow etc.

Regards
Charlie

"Roger Govier" wrote:

Hi Charlie

FormatConditional FormattingFormula is=$A2<$A1Formatchoose what you
want

--
Regards
Roger Govier

"Charlie Bamford" wrote in
message ...
I have a spreadsheet where column A contains a Product Group and it is
sorted
in this order. Each Product Group may be on several Rows. I'd like to
change the colour of the row when the Product Group changes value. i.e.
similar to formatting alternate Rows but based on the change in value in
Column A...How?

Row Format by Group
 

Hi Charlie

I don't think you can do that with CF.

The following macro will probably achieve what you want.

Sub ColourRows()
Dim myrange As Range, userange As Range
Dim start As Range, finish As Range
Dim i As Long, j As Long, k As Long, lc As Long, lr As Long

Set myrange = ActiveSheet.UsedRange
lc = myrange.Columns.Count
lr = myrange.Rows.Count

i = 2
k = 1
Do While i < lr
Set start = Cells(i, 1)
j = WorksheetFunction.CountIf(Range(Cells(2, "A"), Cells(lr + 1,
"A")), start.Value)
Set userange = Range(Cells(i, 1), Cells(i + j - 1, lc))
userange.Select
If k = 1 Then
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
k = 0
Else
With Selection.Interior
.ColorIndex = 40
.Pattern = xlSolid
End With
k = 1
End If
i = i + j
j = 0
Loop

End Sub

To insert the macro into your workbook.
Alt+F11 to invoke the VB Editor
InsertModule
Copy code above and Paste into white pane that appears
Alt+F11 to return to Excel

To use
Alt+F8 to bring up list of Macros
Highlight ColourRowsRun


--
Regards
Roger Govier

"Charlie Bamford" wrote in
message ...
Thanks Roger

This highlights the last row for each Group which helps but it doesn't go
quite as far as I would like. For example, all rows with Group 14DS
should
be filled with yellow. The next 6 rows have Group 18AL. No fill is
required
for these wows. After these comes Group 18BB and the fill should revert
to
yellow etc.

Regards
Charlie

"Roger Govier" wrote:

Hi Charlie

FormatConditional FormattingFormula is=$A2<$A1Formatchoose what you
want

--
Regards
Roger Govier

"Charlie Bamford" wrote in
message ...
I have a spreadsheet where column A contains a Product Group and it is
sorted
in this order. Each Product Group may be on several Rows. I'd like to
change the colour of the row when the Product Group changes value.
i.e.
similar to formatting alternate Rows but based on the change in value
in
Column A...How?

Row Format by Group
 

Sorry about the line wrap on one of the lines.
Substitute this

j = WorksheetFunction.CountIf(Range(Cells(2, "A"), _
Cells(lr + 1, "A")), start.Value)

--
Regards
Roger Govier

"Roger Govier" <roger@technology4unospamdotcodotuk wrote in message
...
Hi Charlie

I don't think you can do that with CF.

The following macro will probably achieve what you want.

Sub ColourRows()
Dim myrange As Range, userange As Range
Dim start As Range, finish As Range
Dim i As Long, j As Long, k As Long, lc As Long, lr As Long

Set myrange = ActiveSheet.UsedRange
lc = myrange.Columns.Count
lr = myrange.Rows.Count

i = 2
k = 1
Do While i < lr
Set start = Cells(i, 1)
j = WorksheetFunction.CountIf(Range(Cells(2, "A"), Cells(lr + 1,
"A")), start.Value)
Set userange = Range(Cells(i, 1), Cells(i + j - 1, lc))
userange.Select
If k = 1 Then
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
k = 0
Else
With Selection.Interior
.ColorIndex = 40
.Pattern = xlSolid
End With
k = 1
End If
i = i + j
j = 0
Loop

End Sub

To insert the macro into your workbook.
Alt+F11 to invoke the VB Editor
InsertModule
Copy code above and Paste into white pane that appears
Alt+F11 to return to Excel

To use
Alt+F8 to bring up list of Macros
Highlight ColourRowsRun


--
Regards
Roger Govier

"Charlie Bamford" wrote in
message ...
Thanks Roger

This highlights the last row for each Group which helps but it doesn't go
quite as far as I would like. For example, all rows with Group 14DS
should
be filled with yellow. The next 6 rows have Group 18AL. No fill is
required
for these wows. After these comes Group 18BB and the fill should revert
to
yellow etc.

Regards
Charlie

"Roger Govier" wrote:

Hi Charlie

FormatConditional FormattingFormula is=$A2<$A1Formatchoose what
you
want

--
Regards
Roger Govier

"Charlie Bamford" wrote in
message ...
I have a spreadsheet where column A contains a Product Group and it is
sorted
in this order. Each Product Group may be on several Rows. I'd like
to
change the colour of the row when the Product Group changes value.
i.e.
similar to formatting alternate Rows but based on the change in value
in
Column A...How?

Row Format by Group
 

Hi,

Thanks so much for this Roger, it has worked extremely well for my
spreadsheet too. I have one question ...

On my spreadsheet, the macro doesn't highlight the very last row. Is there
an adjustment that would allow me to keep the alternating colour pattern
going to include that last row?

Thanks in advance,
Scott

"Roger Govier" wrote:

Sorry about the line wrap on one of the lines.
Substitute this

j = WorksheetFunction.CountIf(Range(Cells(2, "A"), _
Cells(lr + 1, "A")), start.Value)

--
Regards
Roger Govier

"Roger Govier" <roger@technology4unospamdotcodotuk wrote in message
...
Hi Charlie

I don't think you can do that with CF.

The following macro will probably achieve what you want.

Sub ColourRows()
Dim myrange As Range, userange As Range
Dim start As Range, finish As Range
Dim i As Long, j As Long, k As Long, lc As Long, lr As Long

Set myrange = ActiveSheet.UsedRange
lc = myrange.Columns.Count
lr = myrange.Rows.Count

i = 2
k = 1
Do While i < lr
Set start = Cells(i, 1)
j = WorksheetFunction.CountIf(Range(Cells(2, "A"), Cells(lr + 1,
"A")), start.Value)
Set userange = Range(Cells(i, 1), Cells(i + j - 1, lc))
userange.Select
If k = 1 Then
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
k = 0
Else
With Selection.Interior
.ColorIndex = 40
.Pattern = xlSolid
End With
k = 1
End If
i = i + j
j = 0
Loop

End Sub

To insert the macro into your workbook.
Alt+F11 to invoke the VB Editor
InsertModule
Copy code above and Paste into white pane that appears
Alt+F11 to return to Excel

To use
Alt+F8 to bring up list of Macros
Highlight ColourRowsRun


--
Regards
Roger Govier

"Charlie Bamford" wrote in
message ...
Thanks Roger

This highlights the last row for each Group which helps but it doesn't go
quite as far as I would like. For example, all rows with Group 14DS
should
be filled with yellow. The next 6 rows have Group 18AL. No fill is
required
for these wows. After these comes Group 18BB and the fill should revert
to
yellow etc.

Regards
Charlie

"Roger Govier" wrote:

Hi Charlie

FormatConditional FormattingFormula is=$A2<$A1Formatchoose what
you
want

--
Regards
Roger Govier

"Charlie Bamford" wrote in
message ...
I have a spreadsheet where column A contains a Product Group and it is
sorted
in this order. Each Product Group may be on several Rows. I'd like
to
change the colour of the row when the Product Group changes value.
i.e.
similar to formatting alternate Rows but based on the change in value
in
Column A...How?