CODEWITHSUNDEEP
excelvba
Vignesh Subramanian
Vignesh Subramanian

Reputation: 7289

Change font color for a part of text in cell

I have cells which will contain the below value

"Image not allowed|png"

I want to change the color of |png alone or whatever comes after "|"

Now i am trying to change the font color using the below code

Cells(4,2).Font.Color = RGB(255, 50, 25)

It will change the entire cells font color, Is it possible to change only the selected text color(|png) using VBA?

Upvotes: 14

Views: 45956

Answers (3)

R3uK
R3uK

Reputation: 14537

You can use Characters cell's property like :

Cells(1,1).Characters(Start:=2, Length:=3).Font.Color = RGB(255, 0, 0)

This should be a good start :

Sub vignesh()
Dim StartChar As Integer, _
    LenColor As Integer

For i = 1 To 5
    With Sheets("Sheet1").Cells(i, 1)
        StartChar = InStr(1, .Value, "|")
        If StartChar <> 0 Then
            LenColor = Len(.Value) - StartChar + 1
            .Characters(Start:=StartChar, Length:=LenColor).Font.Color = RGB(255, 0, 0)
        End If
    End With
Next i

End Sub

Upvotes: 18

0m3r
0m3r

Reputation: 12499

Is it possible to change only the selected text color

Simple

Option Explicit
Sub Test()
    With Selection.Font
        .ColorIndex = 3 
    End With
End Sub

Upvotes: -1

to StackOverflow
to StackOverflow

Reputation: 124686

Yes this is possible. A good way to explore the Excel object model is to use the macro recorder to record a macro where you manually carry out the manipulation you're interested in.

In this case, you can use:

Cell.Characters(Start:=1, Length:=5).Font

to set font properties of a substring in a cell.

Upvotes: 12

Related Questions