How to return the next value in a row based on whether it is already used in a list

cTennant2 

Right-click the sheet tab.

Select 'View Code' from the context menu.

Copy the code listed below into the worksheet module.

Switch back to Excel.

Save the workbook as a macro-enabled workbook (*.xlsm).

Make sure that you allow macros when you open the workbook.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Long
    Dim s As Long
    Dim v As String
    Dim w As String
    Dim p As Long
    Dim n As Long
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Range("B2:C10000"), Target) Is Nothing Then Exit Sub
    r = Target.Row
    v = Range("B" & r).Value
    If v = "" Or Range("C" & r).Value <> "RED" Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    For s = r - 1 To 2 Step -1
        w = Range("A" & s).Value
        If w Like v & "*" Then
            p = InStr(w, "-")
            If p Then
                n = Mid(w, p + 1)
            End If
            Range("A" & r).Value = v & "-" & n + 1
            Exit For
        End If
    Next s
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub