Changing Font Colour

[branston]

Hi

I have some code which has an text output and I need to change the latter part of the text to a different colour. Is this possible?

For e.g. I have added

<code> & cell.Offset(0, -1).Color = vbRed </code>

to the end of

<code> " ( POSITION NUMBER : " & cell.Offset(0, -1).Value & cell.Offset(0, -1).Color = vbRed & " ) " </code>

but not getting the desired output.

Any help would be appreciated.

TIA

[georgiboy]

Hi Branston,

Below is a sub i used once to change the colour of text within text, it should give you a steer.

Sub ChangeColour(rCell As Range)    
    Dim Counter As Integer
    Dim MyString As String
    
    MyString = rCell.Value
    For Counter = 1 To Len(MyString)
        If Mid(MyString, Counter, 1) = ">" Then
            rCell.Characters(Start:=Counter, Length:=1).Font.Color = vbRed
        ElseIf Mid(MyString, Counter, 1) = "|" Then
            rCell.Characters(Start:=Counter, Length:=1).Font.Color = vbBlue
        End If
    Next
    
End Sub

Hope this helps

[branston]

Thanks. Have tried different variations without joy.

I've added the text before in case it helps.

<code> " [ Points : " & Round(cell.Offset(0, 2).Value * 100, 0) & "% ]" & _ " [ POSITION NUMBER : " & cell.Offset(0, -1).Value & " ] " </code>

I need the 'POSITION NUMBER' bit in line 2 above in a different colour to the 'Points' in line 1.

Anyone?

[snb]

Use the macro recorder while changing the colour of a part of the text in the cell.

[branston]

Thanks snb. The issue I have is that the text string is created dynamically ie. changes depending on the candidate number (and also the text is placed in a pic which was created using the camera tool). I'm not sure how to use the macro recorder in this situation.

Just need to know if I can actually add code like <code>& cell.Offset(0, -1).Color = vbRed</code> to my existing code to not only create the text string but to also change the colour at the end of the string.

Thanks for your help and patience.

[snb]

Just try and adapt the code.

[branston]

Thanks. I have tried and seemed to have had some success by amending the worksheets function. Except now that whole string is Red instead of the just the latter part.

<code>
Worksheets(i).Range("B" & count + 1).Font.Color = vbRed
Worksheets(i).Range("B" & count + 1) = " [ Points : " & Round(cell.Offset(0, 3).Value * 100, 0) & "% ]" & _
" [ POSITION NUMBER : " & cell.Offset(0, -2).Value & " ] "
</code>

How could I change this now so that just the POSITION NUMBER is red ?

[georgiboy]

Could you not use something like this:

Range("A1").Characters(InStr(Range("A1").Value, "POSITION NUMBER"), 15).Font.Color = vbRed

Cheers

[branston]

I've played with it but it's liking any of my combinations............

[p45cal]

Do you want the literal text POSITION NUMBER in red or the value (number) after it? (Or both?)

[georgiboy]

The way i see it whether you want the words or just the number to change colour in the cell then you are going to have to do it after the value has been placed into the cell.

Below is probably not he best example but it is an example of how you could change a specific part of the string colour after the text has been added:

Sub test()    
    Dim cell As Range, pos As Long, str As String
    
    Set cell = Range("B1")

    Range("A2").Value = " ( POSITION NUMBER : " & cell.Offset(0, -1).Value & " ) "
    str = Range("A2").Value
    pos = InStr(str, ":")
    Range("A2").Characters(pos + 1, (Len(str) - pos) - 2).Font.Color = vbRed
End Sub

Hope this helps

[branston]

Hi

I need both …..

thanks

[p45cal]

With Worksheets(i).Range("B" & Count + 1)
  .Value = " [ Points : " & Round(cell.Offset(0, 3).Value * 100, 0) & "% ]" & " [ POSITION NUMBER : " & cell.Offset(0, -2).Value & " ] "
  .Characters(InStr(.Value, "POSITION NUMBER : "), 18 + Len(cell.Offset(0, -2).Value)).Font.Color = vbRed
End With

[branston]

Thanks Pascal - will give that a try.

[branston]

.Characters(InStr(.Value, "POSITION NUMBER : "), 18 + Len(cell.Offset(0, -2).Value)).Font.Color = vbRed

worked a treat - thanks again P45cal !