¿Cómo se puede cambiar el color de la etiqueta de una hoja en Excel, según el criterio de una celda?

Lo importante es indentificar ese criterio y como se actualizará dicho valor, ya que si es algo que ingresas 'a mano', entonces simplemente puedes poner un código en el evento Change de la hoja, en cambio si viene por fórmula ya puede ser un poco más complicado, porque podrías hacer un código que 'se esté ejecutando todo el tiempo' y haga muy lento tu archivo.

Más allá de como lo vayas a hacer el código para cambiar el color que buscas es

    With ActiveWorkbook.Sheets("Hoja1").Tab
        .Color = 5287936 'Para verde
        .TintAndShade = 0
    End With

el color para rojo es 255 (esto lo hice directo con la grabadora de macro, así que podría optimizarse un poco)

Salu2

Gracias por la respuesta tengo una duda, donde puedo comparar si es una opción o es la otra,

Es decir si mi texto es "RETIRADO" la idea es que la pestaña de hoja se ponga de color rojo y si el texto es "ACTIVO" se coloque verde eso es todo.

¿El tema es si esos valores los escribís 'a mano' o son resultado de un cálculo? Si lo ingresás "a mano" (que es la forma más sencilla), entonces en la hoja en cuestión deberías poner el siguiente evento:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
  If Target = "ACTIVO" Then
    With ActiveSheet.Tab
      .Color = 65280
      .TintAndShade = 0
    End With
  ElseIf Target = "RETIRADO" Then
    With ActiveSheet.Tab
      .Color = 255
      .TintAndShade = 0
    End With
  End If
End If
End Sub

Suponiendo que en A1 es donde incluís ese texto

Salu2

Esta excelente cumple con lo prometido, solo le veo un inconveniente y es que cuando la celda A1 está desocupada o sin texto no cambia de color, es decir sin color.

Y por último de donde puedo sacar los valores para los colores