Formatting based on multiple criteria

[nmgmarques]

Hi all:

Consider:

Book1
	A	B	C	D	E	F	G	H
1	N Interno	Ano	ECM	Aplicvel	Assunto	Responsvel	Data	Concluso
2	1	2016	500000011599		Specification 784_0048	Test User	2016.02.10	2016.02.17
3	2	2016	500000011234		Specification 123_54687	Test User	2016.03.10	2016.03.17
4	3	2016	500000011599		Specification 784_0048	Test User	2016.03.11	2016.03.18
5	1	2016	500000011599		Specification 784_0048	Test User	2016.03.12	2016.03.19
6	1	2016	500000011599	A	Specification 784_0048	Test User	2016.03.13	2016.05.11
7	2	2016	500000011234	NA	Specification 123_54687	Test User	2016.03.13	-
Index

This table is updated automatically from another workbook. Whenever a new copy of the other workbook is created, it pushes data to this one, filling in the columns.

When a ECM is created, nothing is highlighted. My issue is when duplicates start to arise. I need to format them based on the case.

• If the ECM exists and data in columns A through E are identical, the C cell should highlight red. This indicates a duplicate entry. Rows 2 and 5.
• If the ECM exists and data in columns C through E are identical and A and B differ, cells A,B and C should highlight red. This indicates someone tried to create a new number for an existing ECM. Rows 2 and 4.
• If the ECM exists and data in columns A through E (with the exception of D) are identical, the C cell should highlight green. This indicates the ECM has been either approved or rejected. Rows 2 and 6; 3 and 7.

The very first occurrence of the ECM number will always be either no fill (in case there is no other mention of the ECM in C) or green. Only the 2nd and following references should trigger the formatting above.

Is this at all possible? I don't believe conditional formatting will cover this.