macro to color all specific letters in a range of text

**WillemB** · 02-14-2014, 03:59 AM

Hello,

I'm trying to set up a macro to color all specific letters in a range of text in one cell. So for example the text ERFLEEWLEFDE, I want to give all the "E" a blue font color and font = bold.
the macro code I came up with only colors the first "E" it finds.
who can help me with some changes in the code to color all the "E" in the text.

thanks in advance

text in cell: ERFLEWLEFDE

code so far:

Sub Neg_Click()
Dim LR As Long, i As Long
LR = Range("G" & Rows.count).End(xlUp).Row
Negcolor = vbBlue

Select Case Neg.Value
Case True
Neg.Caption = "Negative"
Neg.ForeColor = Negcolor
For i = 3 To LR
    With Range("G" & i)
        If InStr(.Value, "E") <> 0 Then .Characters(InStr(.Value, "E"), 1).Font.Color = Negcolor
        If InStr(.Value, "E") <> 0 Then .Characters(InStr(.Value, "E"), 1).Font.Bold = True
    End With
Next i
Case False
Neg.Caption = "Negative"
Neg.ForeColor = vbBlack

For i = 3 To LR
    With Range("G" & i)
        If InStr(.Value, "E") <> 0 Then .Characters(InStr(.Value, "E"), 1).Font.Color = vbBlack
        If InStr(.Value, "E") <> 0 Then .Characters(InStr(.Value, "E"), 1).Font.Bold = False
    End With
Next i
End Select

End Sub

**WillemB** · 02-14-2014, 04:23 AM

Hello,

I'm sorry,
trying to fix it but it then the site is blocked..?

**millz** · 02-14-2014, 04:43 AM

Try

Sub Neg_Click()
    Dim LR As Long, i, k As Long
    LR = Range("G" & rows.Count).End(xlUp).row
    Negcolor = vbBlue
    
    Select Case Neg.Value
        Case True
            Neg.Caption = "Negative"
            Neg.ForeColor = Negcolor
            For i = 3 To LR
                With Range("G" & i)
                    For k = 1 To Len(.Value)
                        If .Characters(k, 1) = "E" Then
                            With .Characters(k, 1)
                                .Font.Color = Negcolor
                                .Font.Bold = True
                            End With
                        End If
                    Next
                End With
            Next i
        Case False
            Neg.Caption = "Negative"
            Neg.ForeColor = vbBlack
            
            For i = 3 To LR
                With Range("G" & i)
                    For k = 1 To Len(.Value)
                        If .Characters(k, 1) = "E" Then
                            With .Characters(k, 1)
                                .Font.Color = vbBlack
                                .Font.Bold = True
                            End With
                        End If
                    Next
                End With
            Next i
    End Select

End Sub

**WillemB** · 02-14-2014, 05:04 AM

Hello,
thanks for your help,
it isn't working, it stuck on (If .Characters(k, 1) = "E" Then)
error: runtime error 438. object doesn't support this property or method

**millz** · 02-14-2014, 05:12 AM

Originally Posted by WillemB

it isn't working, it stuck on (If .Characters(k, 1) = "E" Then)

Sorry about that, missed out .Caption.

Sub Neg_Click()
    Dim LR As Long, i, k As Long
    LR = Range("G" & rows.Count).End(xlUp).row
    Negcolor = vbBlue
    
    Select Case Neg.Value
        Case True
            Neg.Caption = "Negative"
            Neg.ForeColor = Negcolor
            For i = 3 To LR
                With Range("G" & i)
                    For k = 1 To Len(.Value)
                        If .Characters(k, 1).Caption = "E" Then
                            With .Characters(k, 1)
                                .Font.Color = Negcolor
                                .Font.Bold = True
                            End With
                        End If
                    Next
                End With
            Next i
        Case False
            Neg.Caption = "Negative"
            Neg.ForeColor = vbBlack
            
            For i = 3 To LR
                With Range("G" & i)
                    For k = 1 To Len(.Value)
                        If .Characters(k, 1).Caption = "E" Then
                            With .Characters(k, 1)
                                .Font.Color = vbBlack
                                .Font.Bold = True
                            End With
                        End If
                    Next
                End With
            Next i
    End Select

End Sub

**apo** · 02-14-2014, 05:06 AM

Hi.. Try this.. it will color the "e" character in the usedrange in column G in blue...

Private Sub CommandButton1_Click()
    Dim f, x, j As Long
    For Each f In Range("G3:G" & Range("G" & Rows.Count).End(xlUp).Row)
        For j = 1 To Len(f)
            x = Mid(f, j, 1)
            If x = "e" Then
                Range(f.Address).Characters(Start:=InStr(1, f, x), Length:=1).Font.Color = -4165632
            End If
        Next j
    Next f
End Sub

Ooops.. after this line..

Range(f.Address).Characters(Start:=InStr(1, f, x), Length:=1).Font.Color = -4165632

Add this line to make the character be Bold..

 Range(f.Address).Characters(Start:=InStr(1, f, x), Length:=1).Font.Bold = True

**WillemB** · 02-14-2014, 05:13 AM

Thanks for your help

it works!

**WillemB** · 02-14-2014, 05:16 AM

It's Working

Thanks for the help!

**apo** · 02-14-2014, 05:18 AM

This will work now.. just needed to replace the "1" with the "j" variable..

Private Sub CommandButton1_Click()
    Dim f, x, j As Long
    For Each f In Range("G3:G" & Range("G" & Rows.Count).End(xlUp).Row)
        For j = 1 To Len(f)
            x = Mid(f, j, 1)
            If x = "e" Then
                Range(f.Address).Characters(Start:=InStr(j, f, x), Length:=1).Font.Color = -4165632
                Range(f.Address).Characters(Start:=InStr(j, f, x), Length:=1).Font.Bold = True
            End If
        Next j
    Next f
End Sub

**WillemB** · 02-14-2014, 05:47 AM

dear Millz,

the code is working nice,
is it possible to select more letters in once? like the "E" and the "D" ant maybe other letters?

thanks

**apo** · 02-14-2014, 05:52 AM

Hi..
Did you try my code..? I am pretty sure it does what you want..

To select more letters.. just add the "Or" operator..

Example:

Private Sub CommandButton1_Click()
    Dim f, x, j As Long
    For Each f In Range("G3:G" & Range("G" & Rows.Count).End(xlUp).Row)
        For j = 1 To Len(f)
            x = Mid(f, j, 1)
           If x = "e" Or x = "a" Or x = "p" Then
                Range(f.Address).Characters(Start:=InStr(j, f, x), Length:=1).Font.Color = -4165632
                Range(f.Address).Characters(Start:=InStr(j, f, x), Length:=1).Font.Bold = True
            End If
        Next j
    Next f
End Sub

**WillemB** · 02-14-2014, 07:03 AM

Hey Apo,

I'am trying to put it in a toggle button like the code here:
it gives an error; Next without for?
can you help

 
Dim f, x, j As Long
    For Each f In Range("G3:G" & Range("G" & Rows.count).End(xlUp).Row)
Negcolor = vbBlue

Select Case Neg.Value
Case True
Neg.Caption = "Negative"
Neg.ForeColor = Negcolor
        
        For j = 1 To Len(f)
            x = Mid(f, j, 1)
           If x = "E" Or x = "D" Or x = "" Then
                Range(f.Address).Characters(Start:=InStr(j, f, x), Length:=j).Font.Color = vbBlue
                Range(f.Address).Characters(Start:=InStr(j, f, x), Length:=j).Font.Bold = True
            End If
        Next j
    Next f
       
Case False
Ne.Caption = "Negative"
Neg.ForeColor = vbBlack

For j = 1 To Len(f)
            x = Mid(f, j, 1)
           If x = "E" Or x = "D" Or x = "" Then
                Range(f.Address).Characters(Start:=InStr(j, f, x), Length:=j).Font.Color = vbblack
                Range(f.Address).Characters(Start:=InStr(j, f, x), Length:=j).Font.Bold = False
            End If
        Next j
    Next f
End Select
End Sub

**apo** · 02-14-2014, 07:14 AM

hi.. watching a movie right now..

..

but just quickly.. remove the first "Next f" and move the secondd "Next f" below the "End Select" statement.. thats a start anyway..

**WillemB** · 02-14-2014, 08:29 AM

Hi,

Not working yet, I will wait till end of the movie

thanks

**millz** · 02-14-2014, 02:44 PM

You can add more conditions like this:

Sub Neg_Click()
    Dim LR As Long, i, k As Long
    LR = Range("G" & rows.Count).End(xlUp).row
    Negcolor = vbBlue
    
    Select Case Neg.Value
        Case True
            Neg.Caption = "Negative"
            Neg.ForeColor = Negcolor
            For i = 3 To LR
                With Range("G" & i)
                    For k = 1 To Len(.Value)
                        Select Case .Characters(k, 1).Caption
                            Case "E", "D", "F", "Z" 'F and Z just as example.
                                With .Characters(k, 1)
                                    .Font.Color = Negcolor
                                    .Font.Bold = True
                                End With
                        End Select
                    Next
                End With
            Next i
        Case False
            Neg.Caption = "Negative"
            Neg.ForeColor = vbBlack
            
            For i = 3 To LR
                With Range("G" & i)
                    For k = 1 To Len(.Value)
                        Select Case .Characters(k, 1).Caption
                            Case "E", "D", "F", "Z" 'F and Z just as example.
                                With .Characters(k, 1)
                                    .Font.Color = vbBlack
                                    .Font.Bold = True
                                End With
                        End Select
                    Next
                End With
            Next i
    End Select

End Sub

**WillemB** · 02-18-2014, 08:42 AM

It's working thanks,
but if the value of the cells in column G is linked to another cell t gives error 1004 unable to get caption property of the characters class
can you help me with this error

**millz** · 02-19-2014, 08:09 AM

I don't think there's a way to highlight the letters if you are linking cell values. You must change them into values first.

macro to color all specific letters in a range of text

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

macro to color all specific letters in a range of text

Re: macro to color all specific letters in a range of text

Re: macro to color all specific letters in a range of text

Re: macro to color all specific letters in a range of text

Re: macro to color all specific letters in a range of text

Re: macro to color all specific letters in a range of text

Re: macro to color all specific letters in a range of text

Re: macro to color all specific letters in a range of text

Re: macro to color all specific letters in a range of text

Re: macro to color all specific letters in a range of text

Re: macro to color all specific letters in a range of text

Re: macro to color all specific letters in a range of text

Re: macro to color all specific letters in a range of text

Re: macro to color all specific letters in a range of text

Re: macro to color all specific letters in a range of text

Re: macro to color all specific letters in a range of text

Re: macro to color all specific letters in a range of text

Thread Information

Users Browsing this Thread

Similar Threads

how to find a color in a row where the color is blue , if true fill with a specific text

Macro to format row color in a range based on text value in column

Change text color of specific date range by macro in Excel

[SOLVED] how can i get EXcel to add up letters in a specific color?

count by specific text color in range of cell

Bookmarks

Bookmarks

Posting Permissions