XL 2010 Formule excel..

Private Sub Worksheet_Change(ByVal Target As Range)
Dim a As Range
Application.ScreenUpdating = False
Application.EnableEvents = False
On Error Resume Next
With Intersect(Range("C2:C" & Rows.Count), UsedRange)
    .ClearContents 'RAZ
    For Each a In .Offset(, 1).SpecialCells(xlCellTypeConstants).Areas
        a(1, 0) = IIf(Application.CountIf(a, a(1)) = a.Count, a(1), "MIXTE")
    Next
End With
Application.EnableEvents = True
End Sub

Function Espece(colonne As Range) As String
Dim c As Range
Set c = Intersect(Application.Caller.EntireRow, colonne)
If c = "" Or c(0) <> "" And c.Row > 2 Then Exit Function
Espece = c
While c <> ""
    If c <> Espece Then Espece = "MIXTE": Exit Function
    Set c = c(2)
Wend
End Function

=Espece(D:D)

Function Espece(Client As Range, Animaux As Range) As String
Dim c1 As Range, c2 As Range, nom As String
Set c1 = Intersect(Application.Caller.EntireRow, Client)
Set c2 = Intersect(c1.EntireRow, Animaux)
If c1 = c1(0) Then Exit Function
nom = c1
Espece = c2
While c1 = nom
    If c2 <> Espece Then Espece = "MIXTE": Exit Function
    Set c1 = c1(2)
    Set c2 = c2(2)
Wend
End Function