replace and color characters in a cell

[mdavid]

Hi, I need some help.
I have a cell which contains: "term1 *, term2, term3, term4 *, term5 *"

I need to change this content to: "term1, term2, term3, term4, term5"

In other words change the color of the text preceding the " *" (and before ", ") to red, and remove all occurrences of " *"

Appreciate any code snippets

Thanks
David

 

[Chihiro]

Something like below.

Sub Demo()
Dim x, y
Dim i As Long, j as Long
Dim fStr As String
If InStr([A1].Value2, "*") = 0 Then Exit Sub
x = Split([A1].Value2, ",")
For i = 0 To UBound(x)
    If InStr(x(i), "*") Then
        x(i) = Trim(Replace(x(i), "*", ""))
        fStr = IIf(Len(fStr) = 0, i, fStr & "," & i)
    End If
Next
[A1].Value = Join(x, ", ")
y = Split(fStr, ",")
For i = 0 To UBound(y)
    For j = Application.Find(x(y(i)), [A1].Value2) To Application.Find(x(y(i)), [A1].Value2) + Len(x(y(i))) - 1
        [A1].Characters(j, 1).Font.Color = vbRed
    Next
Next
End Sub

Though I'd not recommend individual character formatting in Excel.

 

[p45cal]

Another way. This one works on the selected cells, so select the cells you want to process before you run blah:

Sub blah()
Dim Positions(), cll As Range, xx, idxPosn As Long, StartPosn As Long, i As Long
For Each cll In Selection.Cells
  With cll
  xx = Split(.Value, ",")
  If UBound(xx) > -1 Then
  ReDim Positions(LBound(xx) To UBound(xx), 1 To 2)
  idxPosn = LBound(Positions) - 1
  StartPosn = 1
  For i = LBound(xx) To UBound(xx)
  If InStr(xx(i), " *") > 0 Then
  xx(i) = Replace(xx(i), " *", "")
  idxPosn = idxPosn + 1
  Positions(idxPosn, 1) = StartPosn
  Positions(idxPosn, 2) = Len(xx(i))
  End If
  StartPosn = StartPosn + Len(xx(i)) + 1
  Next i
  .Value = Join(xx, ",")
  .Font.ColorIndex = xlAutomatic
  For i = LBound(Positions) To idxPosn
  .Characters(Start:=Positions(i, 1), Length:=Positions(i, 2)).Font.Color = vbRed
  Next i
  End If
  End With
Next cll
End Sub

 

[mdavid]

Thanks for your replies - will try them and let you know.
p45Cal 'scuse my ignorance butI want to run this on the content of the ActiveCell only, so how would:
"For Each cll in Selection.Cells" read?

Thanks
David

 

[p45cal]

Select only one cell, then run!
If that's not good enough then delete that line and its corresponding Next cll
Then change With cll to With ActiveCell

 

[mdavid]

Thanks Chihiro,
Worked like a charm out of the box. Unbelievable how many nights I spent on this before I asked here for help - but I guess I learnt a bit along the way - maybe one day I'll be a Ninja too.

Thanks, much appreciated
David