• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

64282
 

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.

64284
 

Attachments

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

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

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
=Table1[[#Headers],[Person Number]]:Table1[@[Person Number]]
(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

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

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

vletm

Excel Ninja
Peter Bartholomew
Speed? I'm interesting the result.
I refer to original data, which You could see from my file.
Screenshot 2019-12-02 at 14.16.02.png
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.
 
Top