Color cells containing same date value

[investntrade]

I have an excel sheet which contains multiple rows which have same date (in the first column)

Date	Min (1-10D)	Max (1-10D)
11-Jun-2019​	-3.03%​	0.98%​
27-Jun-2019​	-6.52%​	2.17%​
01-Jul-2019​	-18.97%​	-12.57%​
03-Jul-2019​	-4.92%​	3.72%​
12-Jul-2019​	-5.43%​	-1.74%​
19-Jul-2019​	-11.01%​	-4.80%​
08-Aug-2019​	1.08%​	3.07%​
19-Aug-2019​	-4.99%​	-0.11%​
30-Aug-2019​	-1.30%​	1.99%​
30-Aug-2019​	2.63%​	12.33%​
03-Sep-2019​	-2.91%​	0.62%​
06-Sep-2019​	10.50%​	20.72%​
09-Sep-2019​	-1.87%​	8.62%​
09-Sep-2019​	-5.92%​	2.39%​
11-Sep-2019​	-1.92%​	3.02%​
11-Sep-2019​	-2.70%​	10.24%​
18-Sep-2019​	-1.48%​	5.75%​
20-Sep-2019​	-5.62%​	1.30%​
20-Sep-2019​	6.07%​	10.50%​
23-Sep-2019​	-0.72%​	9.90%​
23-Sep-2019​	-4.53%​	-1.55%​
23-Sep-2019​	-3.43%​	1.25%​
23-Sep-2019​	-9.08%​	-1.71%​
23-Sep-2019​	4.08%​	33.24%​
23-Sep-2019​	-3.10%​	0.93%​
23-Sep-2019​	-9.38%​	-0.84%​

My requirement is to color the cells which have same date, something like the following (basically, color will be a visual indicator that all these rows belong to same date): (pls see attached image)

I need just 2 colors (to alternate between groups of dates - no unique color for each date group).
Single date values can remain white, other dates having multiple rows can be a different color) - basically anything that gives a visual indication that same date rows are grouped under same color.

Any idea how to achieve that (with formulas in a new column, or with any existing excel feature)

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Here is one way. Use a spare column off to the right somewhere and populate as shown below. Copy the formula down as far as you might need. You can then hide the column if you wish.
Then set up Conditional Formatting as shown.

23 08 20.xlsm
	A	B	C	D	E	F
1						1
2	8/08/2019
3	19/08/2019
4	30/08/2019					2
5	30/08/2019					2
6	3/09/2019
7	6/09/2019
8	9/09/2019					1
9	9/09/2019					1
10	11/09/2019					2
11	11/09/2019					2
12	18/09/2019
13	20/09/2019					1
14	20/09/2019					1
15
Multiple dates

Cell Formulas
Range	Formula
F2:F15	F2	=IF(COUNTIF(A$2:A$20,A2)>1,IF(A2=A1,F1,3-LOOKUP(3,F$1:F1)),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A14	Expression	=F2=1	text	NO
A2:A14	Expression	=F2=2	text	NO

 

[investntrade]

Many thanks @Peter_SSs!​

Works well.

Also updated Excel version in profile.

Took me a while to understand the formula.

Other members, if using this solution, then please don't forget to:
1) Put value of 1 in F1 cell
2) In the F2 formula (=IF(COUNTIF(A$2:A$20,A2)>1,IF(A2=A1,F1,3-LOOKUP(3,F$1:F1)),""), change "A$20" to last row value of your data table
3) If Conditional Formatting is appearing complex, then simply use Filter on column F. Mark all '1' in one color, all '2' in other color, and leave the blanks with no color format

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

.. and for updating your details.