Colouring batches of identical cells

[kalabe]

I have a table of data. For ease, say there are four columns, titled division (e.g. Africa, Europe), country, region (north/south), and city. The data is all sorted from column to column. I would like, in each column, to have any of the same batch of data one colour and the next batch a different colour. The effect is similar to the alternate row effect that word or excel can apply to a table automatically but instead I want it done by batches of the same data and for each column to be done separately.

I hope this is semi-understandable. For some reason I'm told I can't post attachments, perhaps because I hardly ever post?

Does anyone have any ideas please?

 

[Eric W]

You can use Conditional Formatting. Let's say your table looks like:

	B	C	D	E
1	Division	Country	Region	City
2	Africa	Egypt	North	Cairo
3	Africa	Egypt	North	Thebes
4	Europe	England	North	Leeds
5	Europe	England	North	London
6	Europe	England	South	Bath
7	Europe	France	North	Paris
8	Europe	France	South	Cannes
9

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet5​

and your batches are defined when the Division, Country, and Region are all the same. Then you can select columns B:E, click Conditional Formatting > New Rule > Use a formula > and enter

=MOD(SUM(SIGN(FREQUENCY(MATCH($B$1:$B1&"|"&$C$1:$C1&"|"&$D$1:$D1,$B$1:$B1&"|"&$C$1:$C1&"|"&$D$1:$D1,0),ROW($D$1:$D1)-ROW($D$1)+1))),2)*(B1<>"")

Click Format... and choose a fill color. In this example, rows 4 5 and 7 will be highlighted.

 

[kalabe]

Eric, thanks for replying but I've not explained very well what I'm trying to achieve. To use your example, in column B, I would want Europe filled with grey, and Africa white. But in column C, I would want Egypt grey, England white, France grey. In other words each column is separately formatted so that the change of data in that particular column prompts a change in fill. But many thanks for having a look. It's not urgent so when I get home later I'll see if I'm able to attach an example from my home pc as I think I'm still not explaining myself well. Thanks again.

 

[Eric W]

That requires a somewhat easier rule. Select B2:D9999 (or however far down you want), click Conditional Formatting > New Rule > Use a formula > and enter:

=(1-MOD(SUMPRODUCT(--(B$1:B1<>B$2:B2)),2))*(B2<>"")

 

[kalabe]

Thanks Eric, works fine - appreciate you taking the time

 

[Eric W]

Glad it works for you.