Row Format by Group

[Charlie Bamford]

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?

 

[Roger Govier]

Hi Charlie

Format>Conditional Formatting>Formula is>=$A2<>$A1>Format>choose what you
want

 

[Charlie Bamford]

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]

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
Insert>Module
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 ColourRows>Run

 

[Roger Govier]

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)

 

[Scopar]

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