Conditional Formatting with random colors

[Gochix2018]

Good evening,
I've been recently playing around with 1 spreadsheet that I'm using at work.
Basically what i need is to display duplicated values on one of our reports.

What I'm trying to do is displaying all duplicated values on table where if value is duplicated either 1 row up or 1 row below then conditional formatting would color those cells with random color like i have displayed it on the picture below...

Formula I'm using to get duplicated values is first by detecting if values is the same from list

Please Login or Register  to view this content.

Then in the next sheet using "Yes" Or "No" inputs displaying these duplicates on the list.

Please Login or Register  to view this content.

Now only thing that's need to be done is to color with random color where duplicates appear next to each other.. Don't know if this is possible without using VBA but waiting on your replies.
Thanks

[FDibbins]

1. Not really sure what you are trying to do using INDIRECT, but see if this will simplify things...
=IF(OR(C2=C1,C2=C3),1,0)

2. When using formulas in CF, you need to specify the color, so I dont think you can get random colors that way. However, if you modify the "test" formula to return a progressively increasing number (1 for teh 1st match, 2 for the 2nd etc), you could probably base the CF on that sequence?

[Gochix2018]

1. Not really sure what you are trying to do using INDIRECT, but see if this will simplify things...
=IF(OR(C2=C1,C2=C3),1,0)

Thanks, that's just another way how to check top and bottom cell for match.
I've came to a point where i could just add these duplicates to pivot table to display only first cell where is duplicate. For example if cell C3 or C4 or C5 is all the same ( duplicates ) then to mark with helper formula that C3 is "MARKED" and C4 and C5 will be unmarked. And then till next duplicates where again first duplicate will be "MARKED"

Any ideas how to do this?

[JeteMc]

In response to the first post, this proposal produces a rotation of fill colors for consecutive duplicates.
1. Column B is populated using: =IF(AND(C2=C3,C2<>C1),SUM(MAX(B$1:B1),1),IF(C2=C1,B1,0))
2. Column C is highlighted using variations of the following as a conditional formatting rule: =AND(B2<>0,MOD(B2,4)=1)
This rule produces four color highlighting, however it could be changed to produce any number of rules from 2 and up.
Let us know if you have any questions.

[Gochix2018]

In response to the first post, this proposal produces a rotation of fill colors for consecutive duplicates.
1. Column B is populated using: =IF(AND(C2=C3,C2<>C1),SUM(MAX(B$1:B1),1),IF(C2=C1,B1,0))
2. Column C is highlighted using variations of the following as a conditional formatting rule: =AND(B2<>0,MOD(B2,4)=1)
This rule produces four color highlighting, however it could be changed to produce any number of rules from 2 and up.
Let us know if you have any questions.

Thanks JeteMc. Works perfect and will do the job.
Only 1 question now. Can i somehow (with a helper formula) display only 1st cell number for each duplicate in let's say G Column.
This would help me to display in pivot table only duplicate occasion where they appear. Not the whole list of them.

See example.

EDITED.

Sorted this now. Thanks for help JeteMc.

If anyone interested here is the formula.

Please Login or Register  to view this content.

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.