CODEWITHSUNDEEP
exceluser-interfacespreadsheet
Badja
Badja

Reputation: 875

Alternate row colour by data in fields in a spreadsheet

Good Afternoon all,

I've had a search but can't find the answer - please direct me if there is one!

I'm looking to make my spreadsheet have better readability to the user. It requires a lot of manual work outside of the sheet, so the less time strain the spreadsheet is, the better.

I know to to use =mod() in Conditional Formatting but this isn't what I'm looking for

I also know about opening the filter drop down, and clicking one cell, pressing down twice and pressing space bar (rinse and repeat) - but I'm not going to do this over 1000 rows...

Is there a way to alternate the colours from a filtered name in excel?

For example:

+---------------+---------------+--------------+
|   Site Code   |   Site Name   | Changed Date |
+---------------+---------------+--------------+
| 000020        | Bobs site     | 28/11/18     | <-- colour 1
| 000020        | Bobs site     | 26/11/18     | <-- colour 1
| 059201        | Julian's      | date         | <-- colour 2
| 059201        | Julian's      | date         | <-- colour 2
| 002237        | etc. 1        | date         | <-- colour 1
| 523878        | etc. 2        | date         | <-- colour 2
| 523878        | etc. 3        | date         | <-- colour 2
+---------------+---------------+--------------+

So rather than by line number, it would be by the name "bobs site" would be one colour, the next in the list would be another colour etc

I would love for this to apply to site code and site name, so when filtering by either, the rows are highlighted correctly.

I can't do this in the =mod() kind of way, as some sites have just one entry, most have 2 and a few can have up to 10

EDIT: Proof of the answer working for future references Working Answer

Upvotes: 2

Views: 308

Answers (2)

BigBen
BigBen

Reputation: 49998

Doable with a helper column and Conditional Formatting with COUNTIF and MOD.

In the helper column:

=OR(A2<>A1,B2<>B1)

which returns TRUE or FALSE if the site code or site name has changed (or not) compared to the previous row.

Then 2 conditional formatting rules:

  1. =MOD(COUNTIF($D$2:$D2,TRUE),2)=0
  2. =MOD(COUNTIF($D$2:$D2,TRUE),2)=1

The mixed reference ($D$2:$D2) in the COUNTIF will allow for each separate section to be coloured alternately as the instances of TRUE are successively added up.

enter image description here

Upvotes: 1

krib
krib

Reputation: 569

One solution; get all uniq values in a seperate column, copy column you want to refer to, paste to new column, remove duplicates. Then select area with data and start refering those values you want to have that color i conditional formatting. enter image description here

Edit With more options use "AND" or "OR" enter image description here

Upvotes: 1

Related Questions