VBA: Format cell colour within table sorted using slicer tools

Hello all, I would like to format full table rows based on the text in column D after the table has been sorted using a slicer tool filtering on column C.



In image 1 is the formatting I would like for the output (this has been manually formatted to show the different desired colours)
Desired.jpg


In image 2 is the problem, when filtered by column c (haulier) cells with the same colour end up 'next' to each other removing the clear delineation between sites.
Problem.jpg

Is the formatting that I want to do possible? It seems like it should be but currently I cannot make it do what I want.



I have provided the spreadsheet with two tabs, tab one shows the desired, tab two the problem if that would help anyone.



Many thanks


GiantLegume

Hello
Select the affected range and in the conditional formatting enter this formula
= MOD (D19; 3) = 1
and assign the color you want.
For the second color change the final 1 to 2

Hello,
Mario

Originally Posted by Marius44

Hello
Select the affected range and in the conditional formatting enter this formula
= MOD (D19; 3) = 1
and assign the color you want.
For the second color change the final 1 to 2

Hello,
Mario

Hi Mario, I have tried that but nothing happens, have you tried it in my demonstration spreadsheet?

Originally Posted by AliGW

Try this:

=MOD(D19,3)=1

You need a comma for a UK locale, not Mario's semi-colon.

Hi Ali, that hasn't worked either. No formatting change in my spreadsheet.
Have you tried it on my example spreadsheet? If so please would you share that one with me so I can see the working formula?

Hello
I apologize for my mistake but in the Italian version we put a semicolon and not a comma. I forgot to correct.
I am attaching the file I worked on.
Hello,
Mario

Hello
My suggestion works, of course with the indicated fix.
In FC you need to enter two formulas:
1) = MOD (D19,3) = 1
2) = MOD (D19,3) = 2
and you will have the colors, which you will choose, alternating.
Hello,
Mario

Hello
This is my workbook

Hello,
Mario

Hello
Since the data does not always have a final number but often it is text, perhaps it is better to change our approach.
See the Sheets("Foglio1")
Copy the range B2: B59 to another free area of ​​the sheet (I used column AA) and eliminate the duplicates.
Then enter the following formulas in FC (one for each color)
= OR ($ B2 = $ AA $ 2, $ B2 = $ AA $ 4, $ B2 = $ AA $ 6) for the first color
= OR ($ B2 = $ AA $ 3, $ B2 = $ AA $ 5, $ B2 = $ AA $ 7) for the second color

I attach my sample workbook showing this.
Hello,
Mario

Hello Mario and AliGW,

Unfortunately I can't make any of the solutions listed above work once the data is filtered. What I am trying to do is format the table after the filters have been applied to make distinguishing between sites being serviced by the same haulier easier.
Do you believe that this is possible? I am swimming out of my excel knowledge pool here and don't want to keep going if what I want to do isn't achievable.

Thank you both for your efforts so far, I really appreciate the replies and my apologies for the delay in replying.

Please let me know your thoughts and thanks again for taking the time.

GiantLegume

Originally Posted by AliGW

Probably, but you may need VBA to do it.

Are there any good resources you can recommend for learning VBA?

Originally Posted by AliGW

How about I move this to the VBA section? Someone expert in code will be able to advise.

If that's what you think would be best, I am happy to follow your lead.

Hi Ali + all,

Would a formatting rule like below work for what I want to do?

Use if fortmatting
If text in this cell is same as text above - format fill 'yellow'
If text in this cell is different - format fill 'blue'
Start conditional = if text above = 'site' format fill 'yellow'
Apply to column D
Use 'copy' format of cell to left for E>G

Apologies if I haven't used the correct terms, still finding my way around.

GiantLegume