How to extend a sting colour forward and backword until space is found ?

[jaimond]

some or a part of a substing has a color.
How to extend the color forward and backword until space is found to make it fully colored with the present colour ?
There are multiple rows, columns and sheets.

 

[Peter_SSs]

(two three)=(two three)

Yes do not want periods, commas, question marks, etc. colored.

So it seems that you want parentheses coloured if adjoing the partly coloured text but not "periods, commas, question marks, etc."
Perhaps you could give a complete list of what is included in "etc." in the above statement so that we can be sure what we need to deal with and what we don't?

 

[jaimond]

(two three)=(two three)

 

[jaimond]

Here it is:
Peter's = Peter's
Ross' = Ross'
(two three)=(two three)
two-three=two-three
2A.pple=2A.pple
. Thankyou. Again = . Thankyou. Again

 

[Peter_SSs]

It isn't clear what sheets, columns and rows to deal with, so see if this sets you in the right direction. It currently only checks A1:C10 on the active sheet.

The above still holds. As there is a lot of checking of individual characters & interactions between the code and the worksheet, the code could be quite slow so it really would be best to restrict its checking of cells as much as possible. Until I know what sheet(s) and what range(s) or column(s) to check, I can't build such a restriction into the code.

As before though, you could see if this code does what you want in A1:C10 of the active sheet.

Sub Extend_Colour()
  Dim rng As Range, c As Range
  Dim s As String
  Dim h As Long, i As Long, j As Long, lens As Long
  
  Set rng = Range("A1:C10")
  Application.ScreenUpdating = False
  For Each c In rng
    With c
      If IsNull(.Font.Color) Then
        s = " " & .Value & " "
        For i = 1 To Len(s) - 2
          If .Characters(i, 1).Font.Color > 0 Then
            h = InStrRev(Left(s, i), " ")
            j = i + 2
            Do Until Mid(s, j, 1) Like "[!a-zA-Z0-9']"
              j = j + 1
            Loop
            .Characters(h, j - h - 1).Font.Color = .Characters(i, 1).Font.Color
            i = j - 1
          End If
        Next i
      End If
    End With
  Next c
  Application.ScreenUpdating = True
End Sub

 

[jaimond]

Thank you.
I want to apply this code in all rows and columns in sheet1.

 

[Peter_SSs]

Thank you.
I want to apply this code in all rows and columns in sheet1.

Just change the Set rng line in the above code to

Set rng = Sheets("Sheet1").UsedRange

 

[jaimond]

Thank you. And "super like" !

 

[Rick Rothstein]

Sub Extend_Colour()
  Dim rng As Range, c As Range
  Dim s As String
  Dim h As Long, i As Long, j As Long, lens As Long
  
  Set rng = Range("A1:C10")
  Application.ScreenUpdating = False
  For Each c In rng
    With c
      If IsNull(.Font.Color) Then
        s = " " & .Value & " "
        For i = 1 To Len(s) - 2
          If .Characters(i, 1).Font.Color > 0 Then
            h = InStrRev(Left(s, i), " ")
            j = i + 2
            Do Until Mid(s, j, 1) Like "[!a-zA-Z0-9']"
              j = j + 1
            Loop
            .Characters(h, j - h - 1).Font.Color = .Characters(i, 1).Font.Color
            i = j - 1
          End If
        Next i
      End If
    End With
  Next c
  Application.ScreenUpdating = True
End Sub

Peter, when I run your code, it appears to color back to the space in front of the word you are coloring even if there is a non-alphanumeric character occurring before then. So for a "word" like (one), the leading parenthesis is colored red while the trailing one isn't. Same if those parentheses are any other non-alphanumeric character (such as quotes, apostrophes, asterisks, etc.). Also, discovered quite by accident... if the cell has a mixture of text using different font names within the same cell, the color appears to be removed completely rather than being extended.

 

[Peter_SSs]

Peter, when I run your code, it appears to color back to the space in front of the word you are coloring even if there is a non-alphanumeric character occurring before then. So for a "word" like (one), the leading parenthesis is colored red while the trailing one isn't. Same if those parentheses are any other non-alphanumeric character (such as quotes, apostrophes, asterisks, etc.).

That is what the OP wanted isn't it? Ref ..
a) The 'Apple' example in post #13, and
b) Post #5 where it was stated that punctuation was only relevant on the right hand side.

Also, discovered quite by accident... if the cell has a mixture of text using different font names within the same cell, the color appears to be removed completely rather than being extended.

Noted, but I'm not going to check or consider that unless the OP finds a problem with the code on their data.

 

[Rick Rothstein]

That is what the OP wanted isn't it? Ref ..
a) The 'Apple' example in post #13, and
b) Post #5 where it was stated that punctuation was only relevant on the right hand side.

Hmm! I am not sure how deeply the OP has consider this all. I can make an argument for the dot and dash (and underbar, colon and a few others) being treated as if it were an alphanumeric character when located between alphanumeric characters, but not as a general rule the way the OP has indicated. The problem with the OP's implied rules is that it depends on what characters are colored. For example...

One.Two becomes One.Two

whereas

One.Two becomes One.Two

Personally, I would think the first coloring should apply to both examples. When one of those characters appear at the end and there is no intervening alpha-numeric character after it before a space is encountered, then I would think it should stop the coloring. In any case, paired symbols (such as parentheses, quote marks, brackets, etc.) should not be colored at all, or both should be colored, but I can see no logic behind coloring only one of the symbols from