Conditional format multiple dates in multiple ranges

[cologg]

Hi
Is there a way to achieve the attached.
I have multiple ranges of dates and sometimes dates match in the ranges, other times they don't, but I'd like to highlight any dates that match, with a different colour per date
I hope that makes sense and please do ask if not.

Thank you in advance

[JohnTopley]

See the attached which uses VBA

Please Login or Register  to view this content.

Formula in C2 (C1 is set to 2 to avoid colour index of 2 which is white)

=IF(AND(COUNTIF($B$2:$B$33,B2)>1,COUNTIF($B$2:$B2,$B2)=1),MAX($C$1:C1)+1,IFERROR(INDEX($C$1:$C1,MATCH($B2,$B$2:$B$33,0)+1),""))

The first part finds the FIRST occurence of a duplicate date and it is flagged with a number.

The second part finds the corresponding number when we find the matching date(s) and allocates the corresponding number.

The VBA code then fills in colour for matching dates.

Click on button to run macro

Column B (with the dates) is a named range called "Date_List"

NOTE: there is a limit of 56 colour codes.

[cologg]

Thank you John, I'll try it now.
You post on a thread that goes round the world and get you answer from someone down the road! Small world.
I'll let you know how I get on

[cologg]

Morning John,
This is great and I feel I should've been clearer with my request...I've attached an example exactly what my sheet looks like. It could have up to 30 section in total.
Is it possible to use the code with this style layout?
Thank you

[JohnTopley]

See attached

NOTE: you are limited to 56 colours so having more than 56 date diffrences (actually 54 as I avoided using Black) will cause a problem. I currently have not allowed for this.

[cologg]

Thank you very much. I really appreciate it.