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]

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.
If you have large ranges and/or cells that contain very long strings the code may take quite a while to run and there may also be unpredictable results.

Sub ExtendColour()
  Dim c As Range
  Dim s As String
  Dim pos1 As Long, pos2 As Long, i As Long, lens As Long
  
  Application.ScreenUpdating = False
  For Each c In Range("A1:C10")
    With c
      If Not IsNumeric(.Font.Color) Then
        s = .Value & " "
        lens = Len(s)
        Do
          pos1 = pos2 + 1
          pos2 = InStr(pos1, s, " ")
          For i = pos1 To pos2 - 1
            If .Characters(i, 1).Font.Color > 0 Then
              .Characters(pos1, pos2 - pos1).Font.Color = .Characters(i, 1).Font.Color
              Exit For
            End If
          Next i
        Loop Until pos2 = lens
      End If
    End With
  Next c
  Application.ScreenUpdating = True
End Sub

 

[Rick Rothstein]

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.
If you have large ranges and/or cells that contain very long strings the code may take quite a while to run and there may also be unpredictable results.

Sub ExtendColour()
  Dim c As Range
  Dim s As String
  Dim pos1 As Long, pos2 As Long, i As Long, lens As Long
  
  Application.ScreenUpdating = False
  For Each c In Range("A1:C10")
    With c
      If Not IsNumeric(.Font.Color) Then
        s = .Value & " "
        lens = Len(s)
        Do
          pos1 = pos2 + 1
          pos2 = InStr(pos1, s, " ")
          For i = pos1 To pos2 - 1
            If .Characters(i, 1).Font.Color > 0 Then
              .Characters(pos1, pos2 - pos1).Font.Color = .Characters(i, 1).Font.Color
              Exit For
            End If
          Next i
        Loop Until pos2 = lens
      End If
    End With
  Next c
  Application.ScreenUpdating = True
End Sub

Hi Peter... are you back?

As for your code... this may be a time where a RegExp solution would be beneficial as words next to punctuation marks will color incorrectly with the code you posted, for example...

One two (three four) five.

or

One two three?

 

[Peter_SSs]

... words next to punctuation marks will color incorrectly with the code you posted ..

The OP didn't mention "words" as such and asked to go forward/back until a space, not until space or punctuation, so I'm not sure that it is incorrect as posted. OP can clarify if required. (I did assume that the end of the string would act like a space though.)

 

[jaimond]

Yes until space [RHS or LHS] or punctuation, [RHS] found.

 

[Rick Rothstein]

Yes until space [RHS or LHS] or punctuation, [RHS] found.

Okay, so you do not want periods, commas, question marks, etc. colored, correct?

What about parentheses (either on the left or right side of the word to be colored)... should they colored or not? For example...

One (two three) four.

What about dashes between words where one of the words is partially colored... what should be colored? For example...

One two-three four.

 

[Peter_SSs]

Further to Rick's questions, what about apostrophes? Exactly what should be coloured in each of these examples?

Example 1: This is Peter's question.
Example 2: This is Ross' question

 

[jaimond]

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

Peter's = Peter's
Ross' = Ross'
(two three) = (two three)
two-three = two-three

 

[Rick Rothstein]

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

(two three)=(two three)

Please explain the above one... are you saying that if multiple words are enclosed in parentheses and only one of those words is partially colored, that all of the words within the parentheses plus the parentheses should get colored?

 

[jaimond]

(two three)=(two three)