Dim oSheet As Worksheet
Dim varCodes, varCats, varColors
Sub ColorCodeSheets()
Dim lngIndex As Long
  varCodes = Split("US*15985347|UK*5296274|BR*65535|AP*12439801|L*6908415", "|")
  For Each oSheet In ThisWorkbook.Sheets
    For lngIndex = 0 To UBound(varCodes)
      FindCat varCodes(lngIndex)
    Next
    Exit For
  Next
lbl_Exit:
  Exit Sub
End Sub
Function FindCat(strCode)
Dim oRng As Range
Dim strAddr As String
Dim strCat As String, lngColor As Long
  strCat = Split(strCode, "*")(0)
  lngColor = CLng(Split(strCode, "*")(1))
  With oSheet.UsedRange
    Set oRng = .Find(What:=strCat, LookIn:=xlValues)
    ProcessFind oRng, lngColor
    strAddr = oRng.Address
    If Not oRng Is Nothing Then
      On Error GoTo Err_GetOut
      Do
        Set oRng = .FindNext(oRng)
        ProcessFind oRng, lngColor
      Loop While Not oRng Is Nothing And oRng.Address <> strAddr
    End If
  End With
Err_GetOut:
  If Err.Number <> 0 Then Err.Clear
  On Error GoTo 0
  Set oRng = Nothing
lbl_Exit:
  Exit Function
End Function
Sub ProcessFind(oRng As Range, lngColor As Long)
Dim varParts
Dim lngLen As Long
  varParts = Split(oRng.Value, "-")
  If UBound(varParts) = 1 Then
    lngLen = varParts(0) / 0.25
  Else
    lngLen = 32
  End If
  oRng.Resize(1, lngLen).Interior.Color = lngColor
lbl_Exit:
  Exit Sub
End Sub