How to vary shading of groups of rows, based on change in value in aparticular column

  • Thread starter Thread starter Dave K
  • Start date Start date
D

Dave K

I am attempting to vary shading of groups of rows, based on change in
value in a particular column.



For example.

Column A Column B
Bob 5
Bob 6
Jeff 3
Jeff 8
Jeff 2
Eric 3
Eric 7
Etc...

In the above data, i need the Bob rows to be highlighted Gray, the
Jeff rows to be highlighted white (or some other different color than
gray, and then the Eric rows to be highlighted gray, etc.

(The data is sorted by the column of interest (i.e., column A)).

I would imagine this can be done with a formula using conditional
formatting. Any suggestions would be appreciated.
 
Select all the data - I wll assume the first "Bob" is in A1
In the Conditional Formatting dialog use formulas such as
=$A1="Bob" and then set the required color
A formula in conditional formatting must evaluate to TRUE or FALSE
best wishes
--
Bernard Liengme
Microsoft Excel MVP
people.stfx.ca/bliengme
email address: remove uppercase characters

REMEMBER: Microsoft is closing the newsgroups; We will all meet again at
http://answers.microsoft.com/en-us/office/default.aspx#tab=4
 
There are commercial alternatives.
--
Jim Cone
Portland, Oregon USA
( http://tinyurl.com/ShadeData )



"Dave K" <[email protected]>
wrote in message I am attempting to vary shading of groups of rows, based on change in
value in a particular column.
For example.

Column A Column B
Bob 5
Bob 6
Jeff 3
Jeff 8
Jeff 2
Eric 3
Eric 7
Etc...

In the above data, i need the Bob rows to be highlighted Gray, the
Jeff rows to be highlighted white (or some other different color than
gray, and then the Eric rows to be highlighted gray, etc.
(The data is sorted by the column of interest (i.e., column A)).
I would imagine this can be done with a formula using conditional
formatting. Any suggestions would be appreciated.
 
Just to expand on Bernard's answer, for the example given, the formula would
need to be something like:

=($A1="Bob")+($A1="Eric")

However, if you have many different values in A, and/or you can't be sure
what they might be, you would need to use a helper column. e.g:

in D1:

=1

in D2:Dx

=IF($A2=$A1,D1,MOD(D1+1,2))

Then in Conditional Formatting, you need only use:

=D1


HTH
Steve D.
 
Or a macro solution.

Sub Alternate_Row_Color()
'color rows with change in data in column A
'grey, none, grey, none
Dim rngName As Range
Dim colIdx As Integer
Dim i As Long
'Following assumes column header in row 1
Set rngName = Sheets("Sheet1").Range(Cells(1, 1), _
Cells(Rows.Count, 1).End(xlUp))
colIdx = 15 'Grey
'colIdx = xlColorIndexNone
With rngName
'Color the first data row grey
.Cells(1, 1).entirerow.Interior.ColorIndex = colIdx

'Starting at 2nd data row
For i = 2 To .Rows.Count
If .Cells(i) <> .Cells(i - 1) Then
If colIdx = 15 Then
colIdx = xlColorIndexNone
Else
colIdx = 15
End If
End If
.Cells(i).entirerow.Interior.ColorIndex = colIdx
Next i
End With

End Sub


Gord Dibben MS Excel MVP
 
Back
Top