multiple conditions conditional formatting

See attached example.

I used this formula, authored while A1 was selected, applied to columns A:E

Please Login or Register  to view this content.

That defines a single rule - if you want multiple colors, you would need multiple rules. Does this make sense?

Selecting your range (i.e. A2:E15) Conditional Formatting >New Rule>Use Formula

=COUNTIFS($B$2:$B$15,$B2, $C$2:$C$15,$C2, $D$2:$D$15,$D2)>1

Not sure what you mean by "I would prefer the fill to change"
Do you mean that you want sets of matching values to change to a different color (i.e. set 1 is green, set 2 is blue, set 3 is red)? I think that would require VBA.

Okay, decided to work through it with formula's. You will, as the General said, need to set up a color format for each set of duplicates, triplicates, etc but, is possible with a few helper columns that you can hide.

In G2 copied down

=COUNTIFS($C$2:$C$15,$C2, $D$2:$D$15,$D2, $E$2:$E$15,$E2)

Created a concatenated string of the columns you need to match in Column A

A2= C2&D2&E2

In H2 copied down

=IF(G2>1, IF(COUNTIFS($C$2:C2,C2,$D$2:D2,D2,$E$2:E2,E2)>1, INDEX($H$1:H1, MATCH(C2&D2&E2,$A$1:$A1,0)), MAX($H$1:H1)+1),"")

Then with conditional formatting >New Rule>Use Formula

= $H2=1 Format as red bold
then
=$H2=2 Format as Blue bold
and so on for as many duplicates as you think you will have