¿Cómo cambiar el color de la pestaña de una hoja de forma condicional

Para poder realizar lo que pides, deberás poner el siguiente código en la hoja en la cual tienes los datos

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Rng As Range
        With Sheets("Hoja1").Range("H4:H23")
            Set Rng = .Find(What:="No", _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
        End With
If Rng Is Nothing Then
Sheets("Hoja1").Tab.ColorIndex = xlColorIndexNone
Else
Sheets("Hoja1").Tab.ColorIndex = 3
End If
End Sub

Tendrás que cambiar "Hoja1" por el nombre de tu hoja

Cada vez que haya un cambio en la hoja, si en el rango H4:H23 existe la palabra "No", el color de la pestaña será rojo, sino no tendrá color

Muchas gracias!

Me pone que "las siguientes características no se pueden guardar en libros sin macros: Proyecto de VB". Entonces lo guardo como Libro de Excel habilitado para macros (desconocía que eso existiera), pero al abrir ese nuevo excel, ya no funciona el código.

¿Has mirado que en el nuevo excel xlsm el código esté en la hoja que debes evaluar?

Sí está, sí. También he probado de quitar el código y volverlo a poner, y nada. El icono del Excel me sale como el normal del excel con un símbolo amarillo con el signo de exclamación en negro. No se si es el normal de excels con macros o es que hay un error...