Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: delete text within a cell which is a specific color and retain formatting of the remaining text

  1. #1
    New Member
    Join Date
    Jan 2013
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default delete text within a cell which is a specific color and retain formatting of the remaining text

    Hi - i've written following code which is intended to delete all text in cells within a range which are in a specific color. It does indeed delete the text of the specified color - however the formatting of the remaining text is altered / not maintained. Would you have any advice?

    e.g. "are you opened open on sunday?" should become: "are you open on sunday?"

    Code>>>>

    Sub deletecolor()
    Dim rng As Range, sTemp As String, ct As Long
    Set rng = Range("A1:A10")
    For Each c In rng
    ct = 0
    sTemp = vbNullString
    For i = Len(c.Text) To 1 Step -1
    If c.Characters(i, 1).Font.Color = RGB(0,0,0) Then
    ct = ct + 1
    If ct = 1 Then
    sTemp = WorksheetFunction.Replace(c.Text, i, 1, "")
    Else
    sTemp = WorksheetFunction.Replace(sTemp, i, 1, "")
    End If
    End If
    Next i
    If ct > 0 Then c.Value = sTemp
    Next c
    End Sub

  2. #2
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    39,414
    Post Thanks / Like
    Mentioned
    78 Post(s)
    Tagged
    16 Thread(s)

    Default Re: delete text within a cell which is a specific color and retain formatting of the remaining text

    Welcome to the MrExcel board!

    Your descriptions and code do not seem to match. You say you want to delete text of a "specific color". From your expected results it would appear that the color to delete is red as your expected result retains the green text.

    You also say your code deletes the specified color text, implying that the only problem is the color of the remaining text. However, my testing shows that the existing code deletes both the red (opened) and the green (open) text.

    Assuming that what you want to do is delete the red text only and keep the rest, including green, try this in a copy of your workbook.

    Note that it only tries to replicate font colour and any underlining. If you want more (eg Bold) you would need to add extra dimensions to the array aCol and collect and re-write that info by expanding the relevant sections of the code. Post back with more details if you want to do that and need help.


    Sub deletecolor_v2()
      Dim rng As Range, c As Range
      Dim s As String, sTemp As String
      Dim aCol
      Dim p As Long, i As Long, k As Long, L As Long
      
      Set rng = Range("A1:A10")
      For Each c In rng
        s = c.Value
        L = Len(s)
        If L > 0 Then
          k = 0
          sTemp = vbNullString
          ReDim aCol(1 To L, 1 To 2)
          For p = 1 To L
            If c.Characters(p, 1).Font.Color <> vbRed Then
              k = k + 1
              aCol(k, 1) = c.Characters(p, 1).Font.Color
              aCol(k, 2) = c.Characters(p, 1).Font.Underline
              sTemp = sTemp & Mid(s, p, 1)
            End If
          Next p
          c.Value = sTemp
          For p = 1 To k
            c.Characters(p, 1).Font.Color = aCol(p, 1)
            c.Characters(p, 1).Font.Underline = aCol(p, 2)
          Next p
        End If
      Next c
    End Sub
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  3. #3
    New Member
    Join Date
    Jan 2013
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: delete text within a cell which is a specific color and retain formatting of the remaining text

    Peter! thank you very much for your kind help - this has indeed solved my problem - a big thank you from my wife!!!

    brgds,

    Osman

  4. #4
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: delete text within a cell which is a specific color and retain formatting of the remaining text

    Another possibility using the Characters' Delete method:

    Code:
    Sub deletecolor()
        Dim rng As Range
        Dim c As Range
        Dim i As Long
        Set rng = Range("A1:A10")
        For Each c In rng
            For i = Len(c.Text) To 1 Step -1
                With c.Characters(i, 1)
                    If .Font.ColorIndex = 3 Then
                        .Delete
                    End If
                End With
            Next i
        Next c
    End Sub
    Microsoft MVP - Excel

  5. #5
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    39,414
    Post Thanks / Like
    Mentioned
    78 Post(s)
    Tagged
    16 Thread(s)

    Default Re: delete text within a cell which is a specific color and retain formatting of the remaining text

    Quote Originally Posted by Andrew Poulsom View Post
    Another possibility using the Characters' Delete method:
    So simple and Direct Andrew.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •