Ciao,
dovresti farcela così:

codice:
Private Sub CommandButton1_Click()
   
   Set area = Range("a1:e10")
   giallo = RGB(255, 255, 0)
   rosso = RGB(255, 0, 0)
   
   counter = 0
   ultima_colonna = area.Column + 4
   
   For Each cella In area
      If cella.Interior.Color = giallo Then
         cella.Clear
      Else
         If cella.Interior.Color = rosso Then
            counter = counter + 1
         End If
      End If
      If cella.Column = ultima_colonna And counter > 0 Then
         Cells(cella.Row, ultima_colonna + 1) = counter
         counter = 0
      End If
   Next
End Sub

Devi solo inserire nelle prime tre righe il range di dati da esaminare (escludendo la colonna delle date, solo le cinque colonne di numeri), e poi la composizione RGB del giallo e del rosso che usi in modo che possano essere riconosciuti.

Riguardo al range puoi anche indicarne uno più lungo, in alto o in basso, di quello reale in modo che possa gestire diverse quantità di dati senza dover ogni volta aggiornare il codice.

La composizione RGB la trovi facilmente sullo stesso excel selezionando la freccetta dell'icona "colore riempimento" e poi andando su "altri colori" e su "personalizzati".