셀값에서 "-" 제외한 숫자만 추출하여 바로 위 정수로 다른 셀에 지동입력되는 함수

Function NewColorNum(ColorName As String, Target As Range)
  Dim sCode As Variant, iNum As Long, vData As Variant, vItem As Variant
  
  sCode = Application.VLookup(ColorName, Sheets("Sheet1").Range("C1:D100").Value2, 2, False)
  If IsError(sCode) Then NewColorNum = CVErr(xlErrValue): Exit Function
  
  vData = Application.Index(Target.Value2, 0, 1)
  
  For Each vItem In vData
      If InStr(1, vItem, sCode, vbTextCompare) Then
          If InStr(1, vItem, "-") Then
              iNum = Application.Max(iNum, Split(vItem, "-")(1) * 1)
          End If
      End If
  Next
  
  NewColorNum = sCode & "-" & iNum + 1
  
End Function