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

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.
 
B

Bernard Liengme

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
 
J

Jim Cone

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.
 
S

Steve Dunn

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.
 
G

Gord Dibben

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
 
P

PatLee

Good to hear.

Thanks.




- Show quoted text -
Click to expand...

Gord,
you are an expert in writing macro, would you please advise a site for
learning this?

Thanks in advance.
Pat
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Filter out rows based on the value of a cell 4
Filling in blank rows in a Table of data 2
Need to count no. of rows where cell in column A = "value1" AND cellin column B (same row) = "value2 2
Shading alternate "groups" of rows? 10
Alternate row shade by change in key field 3
how to populate a range with another range of data ? 2
Repeat values in column A a certain number of times depending on thevalue in column B 8
Summing rows based on criteria in another column 2

Top