Hi All,
I dabble in using VBA but am no pro by any means. I'm working with the code below, (which another user was kind enough to help me with), my issue develops when I try to format multiple fonts/colors into a single cell based on the character position within the cell. The code I'm having an issue with is highlighted in RED, hopefully it's something simple I'm overlooking. Thanks in advance for any help/hints!!
John
Sub TrimTeam1()
Dim myRng As Range, c As Range
Set myRng = Range("M5:DO67")
With myRng.Font
.FontStyle = "Regular"
.Size = 28
End With
For Each c In myRng
With c
Select Case .Value
Case Is = Chr(152): .Font.Name = "Wingdings 2"
.Font.Color = vbGreen
Case Is = Chr(187): .Font.Name = "Wingdings 2"
.Font.Color = vbGreen
Case Is = Chr(162): .Font.Name = "Wingdings"
.Font.Color = vbGreen
Case Is = Chr(88): .Font.Name = "Webdings"
.Font.Color = vbBlack
Case Is = Chr(112): .Font.Name = "Wingdings 3"
.Font.Color = vbBlue
Case Is = Chr(163): .Font.Name = "Wingdings 2"
.Font.Color = vbBlue
Case Is = Chr(174): .Font.Name = "Wingdings 2"
.Font.ColorIndex = 45
Case Is = Chr(76): .Font.Name = "Helviteca"
.Font.ColorIndex = 45
Case Is = Chr(85): .Font.Name = "Wingdings 2"
.Font.Color = vbBlack
Case Is = Chr(84): .Font.Name = "Wingdings 2"
.Font.Color = vbBlack
Case Is = Chr(204): .Font.Name = "Wingdings 2"
.Font.Color = vbBlack
Case Is = (Chr(187) & Chr(88)): .Characters(Start:=1, Length:=1).Font.Name = "Wingdings 2"
.Characters(Start:=2, Length:=1).Font.Name = "Webdings"
.Characters(Start:=1, Length:=1).Font.Color = vbGreen
.Characters(Start:=2, Length:=1).Font.Color = vbBlack
Case Is = (Chr(187) & Chr(204)): .Font.Name = "Wingdings 2"
.Characters(Start:=1, Length:=1).Font.Color = vbGreen
.Characters(Start:=2, Length:=1).Font.Color = vbBlack
Case Is = (Chr(162) & Chr(204)): .Characters(Start:=1, Length:=1).Font.Name = "Wingdings"
.Characters(Start:=2, Length:=1).Font.Name = "Wingdings 2"
.Characters(Start:=1, Length:=1).Font.Color = vbGreen
.Characters(Start:=2, Length:=1).Font.Color = vbBlack
Case Is = Chr(70): .Font.Name = "Wingdings 2"
.Font.ColorIndex = 15
Case Else: .Font.Name = "Times"
.Font.Color = vbRed
End Select
End With
Next c
End Sub
I dabble in using VBA but am no pro by any means. I'm working with the code below, (which another user was kind enough to help me with), my issue develops when I try to format multiple fonts/colors into a single cell based on the character position within the cell. The code I'm having an issue with is highlighted in RED, hopefully it's something simple I'm overlooking. Thanks in advance for any help/hints!!
John
Sub TrimTeam1()
Dim myRng As Range, c As Range
Set myRng = Range("M5:DO67")
With myRng.Font
.FontStyle = "Regular"
.Size = 28
End With
For Each c In myRng
With c
Select Case .Value
Case Is = Chr(152): .Font.Name = "Wingdings 2"
.Font.Color = vbGreen
Case Is = Chr(187): .Font.Name = "Wingdings 2"
.Font.Color = vbGreen
Case Is = Chr(162): .Font.Name = "Wingdings"
.Font.Color = vbGreen
Case Is = Chr(88): .Font.Name = "Webdings"
.Font.Color = vbBlack
Case Is = Chr(112): .Font.Name = "Wingdings 3"
.Font.Color = vbBlue
Case Is = Chr(163): .Font.Name = "Wingdings 2"
.Font.Color = vbBlue
Case Is = Chr(174): .Font.Name = "Wingdings 2"
.Font.ColorIndex = 45
Case Is = Chr(76): .Font.Name = "Helviteca"
.Font.ColorIndex = 45
Case Is = Chr(85): .Font.Name = "Wingdings 2"
.Font.Color = vbBlack
Case Is = Chr(84): .Font.Name = "Wingdings 2"
.Font.Color = vbBlack
Case Is = Chr(204): .Font.Name = "Wingdings 2"
.Font.Color = vbBlack
Case Is = (Chr(187) & Chr(88)): .Characters(Start:=1, Length:=1).Font.Name = "Wingdings 2"
.Characters(Start:=2, Length:=1).Font.Name = "Webdings"
.Characters(Start:=1, Length:=1).Font.Color = vbGreen
.Characters(Start:=2, Length:=1).Font.Color = vbBlack
Case Is = (Chr(187) & Chr(204)): .Font.Name = "Wingdings 2"
.Characters(Start:=1, Length:=1).Font.Color = vbGreen
.Characters(Start:=2, Length:=1).Font.Color = vbBlack
Case Is = (Chr(162) & Chr(204)): .Characters(Start:=1, Length:=1).Font.Name = "Wingdings"
.Characters(Start:=2, Length:=1).Font.Name = "Wingdings 2"
.Characters(Start:=1, Length:=1).Font.Color = vbGreen
.Characters(Start:=2, Length:=1).Font.Color = vbBlack
Case Is = Chr(70): .Font.Name = "Wingdings 2"
.Font.ColorIndex = 15
Case Else: .Font.Name = "Times"
.Font.Color = vbRed
End Select
End With
Next c
End Sub