Conditional Formatting - color groups based on row value

[djangeroo]

Hi,
I am new to the forum and impressed by the great advise that's out there.

This is my problem:
A list with numbers in column A, these numbers can be used over multiple rows, sometimes 3, 5 or more/less.
Every time this number changes, the color for the entire row should flip between yellow and no color.
Sequence of the number in column A is not always odd / even. I tried various suggestions regarding this topic but I do not get it straight.

Thanks for your help.

 

[lenze]

And likewise, follow this and related threads.

http://www.mrexcel.com/board2/viewtopic.php?t=137021&highlight=

lenze

 

[erik.van.geit]

lenze,
following your link, I had to click another link, then again and again

can't find the tree in the wood
which post are you refering to ?

best regards,
Erik

 

[Yogi Anand]

Now Erik ... here is one without the helper column ... (in the right thread this time) ...

Book2
	A	B	C	D
1
2
3	1
4
5	2
6
7	2
8
9	3
10
11	2
12
13	2
14
Sheet8

formula in cell A1 is ...

=$A1=cell("contents")

and to help trigger the highliting (as in my other post: http://www.mrexcel.com/board2/viewtopic.php?t=181451&start=20), I used the following code ...

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    Application.ScreenUpdating = True
End Sub

 

[erik.van.geit]

Yogi,
but this was not the goal
or do I (again ) miss something ?

A
A
A
B
B
C
C
D
D
D
and sorting won't disturb the colors ...

best regards,
Erik

 

[Yogi Anand]

Hi Erik:

You may be right. In my proposed solution, let us say the cells containing 2 will be highlited yellow, even if the entries in the cells are changed, the highliting will follow the changed entry ... but my cursor has to remain in the cell with the entry to be highlited.

Perhaps I am the one who missed something ... you guys were well into it when I joined in later ... and I may not have understood all of the OP's requirements.

 

[erik.van.geit]

yeah, in THIS thread it was a question of "alternating format"
each time the value changes the color changes (using one color and "default-white")

enjoyed our meetings this night !!
going to sleep right now

 

[Yogi Anand]

I can modify my Conditional Formatting formula to ...

=and($A1=cell("contents"),$A1<>"')

to ensure that when the cursor is moved to a blank cell, no rows are highlited.

However, I don't know whether even with this modification, I am meeting all of the specifications of the OP.

 

[lenze]

Erik, sorry about the confusion.

It's the 2nd response here

http://www.mrexcel.com/board2/viewtopic.php?t=25062&highlight=conditional+formatting+countif

and my reponse here.

http://www.mrexcel.com/board2/viewtopic.php?t=17234

lenze

 

[erik.van.geit]

thanks, lenze,

it was clear where to go now
the formulasystem needs two columns and does not handle correctly non-sorted lists like this one, where the same items are not adjacent
 Smith
 Smith
 Taylor
 Taylor
 Ulmer
 Ulmer
 Ulmer
 Smith
 Smith
unless I'm missing something?

the formulas we displayed earlier, thanks to NBVC, based on what you can find here
http://www.mvps.org/dmcritchie/excel/condfmt.htm
seem to work always to my sense sorted or not

best regards,
Erik

 

[Peter_SSs]

What about this one? The only thing with this one is that you apply the Conditional Formatting formula to the whole data range EXCEPT the first row. So in the example below, select A3:D11 and apply the Conditional Formatting.. Formula is:
=MOD(SUMPRODUCT(--($A$2:$A2<>$A$3:$A3)),2)

If data is positioned elsewhere, replace the $A$2 with the top left cell in the DATA range (that is, the cell ABOVE the top left of the SELECTED range) and replace the $A$3 with the top left cell of the SELECTED range (that is the left cell in the SECOND row of the DATA range) and change the $A2 and $A3 accordingly.

Mr Excel.xls
	A	B	C	D	E
1	Heading	Heading	Heading	Heading
2	John	1	2	3
3	John	1	2	3
4	Jack	1	2	3
5	Jack	1	2	3
6	Marg	1	2	3
7	Bart	1	2	3
8	Maggie	1	2	3
9	Maggie	1	2	3
10	Del	1	2	3
11	Del	1	2	3
12
Sheet1 (3)

Edit: I've joined this thread late and may have missed something... possibly in one of the linked threads? or just going blind? I note that one of Erik's responses mentions sorting, but I couldn't see where this was referred to. My suggestion will not cope too well with subsequent sorting.