# Conditional Formatting / Macro Help - Format a data based on every change in a particular row

#### anishms

##### New Member
Is there any way to format a data based on every change in a particular row. As highlighted below, the colour has changed based on every change in personal number. Preferably two alternate colours

#### Peter Bartholomew

##### Well-Known Member
What is needed is a formula based on relative references to the current and previous rows. The idea is to increment the block counter when the Person Number is different from the previous. The conditional format should paint the cells yellow if the block counter is an odd number.

#### Attachments

• 17.3 KB Views: 4

#### NARAYANK991

##### Excel Ninja
Hi ,

In case you find Peter's solution somewhat overwhelming , see if this is OK.

1. In any unused column , say column D , enter the number 1 in the first row of data ; I have entered 1 in cell D3.

2. In cell D4 enter the following formula , and copy down.

=IF(C4=C3, D3, -D3)

3. For the formatting , select the data range \$C\$3:\$C\$17 , enter the following rules and select two different colours.

=\$D3 = 1 for one colour

=\$D3 = -1 for the second colour

Narayan

#### Attachments

• 9.9 KB Views: 3

#### NARAYANK991

##### Excel Ninja
Hi ,

If you do not want to use a helper column , then select your entire data range and format the cells in one colour.

Then , keeping the entire data range selected , add the following conditional formatting rule and select a different colour.

=MOD(SUM(IF(FREQUENCY(C\$2:C3,C\$2:C3) >0, 1)), 2) = 1

or

=MOD(SUM(--(FREQUENCY(C\$2:C3,C\$2:C3) >0)), 2) = 1

Narayan

#### Attachments

• 9.4 KB Views: 4

#### Peter Bartholomew

##### Well-Known Member
NARAYAN
I loved your use of FREQUENCY; it took me a while to understand its role in the calculation. The version below builds from the same basis but uses SIGN and ISODD functions instead of value comparisons
=ISODD(SUM(SIGN(FREQUENCY(C\$2:C3,C\$2:C3)))

Because, unlike the rest of humanity, I can neither read, nor see the relevance of direct cell referencing, I defined a Named range 'above' to refer to
(or a dynamic array equivalent =top:"@"PNum)

To my mind, this allows a further 'simplification' (or, to others, adds 'complexity')
= ISODD( SUM( SIGN( FREQUENCY(above, above) ) ) )

Another thought is that it would be far easier to move away from the alternating colour fills and, instead, use conditionally formatted borders between the data blocks.

#### Attachments

• 11 KB Views: 1
Last edited:

#### vletm

##### Excel Ninja
anishms
Based Your previous threads more realistic ~1200 rows data this could do same time by [ Do It ].
... and same time I found one missing line from code, which would give more correct results for Your previous thread too.

#### Attachments

• 370.7 KB Views: 0
Last edited:

#### Peter Bartholomew

##### Well-Known Member
@vletm
Interesting that I normally avoid VBA to speed a workbook up whereas you seem to move in the opposite direction.
In this case you would gain by avoiding the partial summations at every row, an O(n²) operation, and presumably also avoid the volatile nature of conditional formatting (your VBA project seemed to be password protected)

Following up on the idea of lower key conditional formatting the example below requires only that the row with a new person ID is formatted, which is far easier to achieve.

#### vletm

##### Excel Ninja
Peter Bartholomew
Speed? I'm interesting the result.
I refer to original data, which You could see from my file.

Conditional formatting with large data ... the original data has 332 rows and 'normally' there are ~1200 rows.
... and same time could do other 'duties' too with one run.