s there a way to replace specific letters in a range of cells with symbols?

[NIKOG]

s there a way to replace specific letters in a range of cells with symbols?

For instance:

Any "S" found would be replaced by the "spade" symbol.
Any "H" found would be replaced by the "heart" symbol.
Any "C" found would be replaced by the "club" symbol.
Any "D" found would be replaced by the "diamond" symbol.
please help me out step by step im really newby - Thanks infront guys

 

[mumps]

It's hard to work with a picture. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file (de-sensitized if necessary) that you can post here.

 

[NIKOG]

It's hard to work with a picture. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file (de-sensitized if necessary) that you can post here.

Dropbox

www.dropbox.com

thisi s the file with the imported macro

 

[NIKOG]

do need also to change anything on the second macro u send like the first 1 i changed the ranges ?

 

[mumps]

Try:

Sub ReplaceLetters()
    Application.ScreenUpdating = False
    Dim rng As Range, cnt As Long
    For Each rng In Range("A2", Range("A" & Rows.Count).End(xlUp))
        If rng Like "*h*" Or rng Like "*c*" Or rng Like "*d*" Or rng Like "*s*" Then
            rng.Replace "h", ChrW(9829)
            rng.Replace "d", ChrW(9830)
            rng.Replace "c", ChrW(9827)
            rng.Replace "s", ChrW(9824)
        End If
    Next rng
    For Each rng In Range("A2", Range("A" & Rows.Count).End(xlUp))
        For cnt = Len(rng) To 1 Step -1
            Select Case AscW(Mid(rng, cnt, 1))
                Case Is = 9829
                    Range("A" & rng.Row).Characters(cnt, 1).Font.ColorIndex = 3
                Case Is = 9830
                    Range("A" & rng.Row).Characters(cnt, 1).Font.ColorIndex = 44
                Case Is = 9827
                    Range("A" & rng.Row).Characters(cnt, 1).Font.ColorIndex = 4
                Case Is = 9824
                    Range("A" & rng.Row).Characters(cnt, 1).Font.ColorIndex = 1
            End Select
        Next cnt
    Next rng
    Application.ScreenUpdating = True
End Sub

 

[NIKOG]

Try:

Sub ReplaceLetters()
    Application.ScreenUpdating = False
    Dim rng As Range, cnt As Long
    For Each rng In Range("A2", Range("A" & Rows.Count).End(xlUp))
        If rng Like "*h*" Or rng Like "*c*" Or rng Like "*d*" Or rng Like "*s*" Then
            rng.Replace "h", ChrW(9829)
            rng.Replace "d", ChrW(9830)
            rng.Replace "c", ChrW(9827)
            rng.Replace "s", ChrW(9824)
        End If
    Next rng
    For Each rng In Range("A2", Range("A" & Rows.Count).End(xlUp))
        For cnt = Len(rng) To 1 Step -1
            Select Case AscW(Mid(rng, cnt, 1))
                Case Is = 9829
                    Range("A" & rng.Row).Characters(cnt, 1).Font.ColorIndex = 3
                Case Is = 9830
                    Range("A" & rng.Row).Characters(cnt, 1).Font.ColorIndex = 44
                Case Is = 9827
                    Range("A" & rng.Row).Characters(cnt, 1).Font.ColorIndex = 4
                Case Is = 9824
                    Range("A" & rng.Row).Characters(cnt, 1).Font.ColorIndex = 1
            End Select
        Next cnt
    Next rng
    Application.ScreenUpdating = True
End Sub

THANKS ALOT MAN U ARE THE BEST CHEERS : 1 last question
can "diamond" symbol be be blue color what i need to change in the macro

Gyazo

 

[NIKOG]

Try:

Sub ReplaceLetters()
    Application.ScreenUpdating = False
    Dim rng As Range, cnt As Long
    For Each rng In Range("A2", Range("A" & Rows.Count).End(xlUp))
        If rng Like "*h*" Or rng Like "*c*" Or rng Like "*d*" Or rng Like "*s*" Then
            rng.Replace "h", ChrW(9829)
            rng.Replace "d", ChrW(9830)
            rng.Replace "c", ChrW(9827)
            rng.Replace "s", ChrW(9824)
        End If
    Next rng
    For Each rng In Range("A2", Range("A" & Rows.Count).End(xlUp))
        For cnt = Len(rng) To 1 Step -1
            Select Case AscW(Mid(rng, cnt, 1))
                Case Is = 9829
                    Range("A" & rng.Row).Characters(cnt, 1).Font.ColorIndex = 3
                Case Is = 9830
                    Range("A" & rng.Row).Characters(cnt, 1).Font.ColorIndex = 44
                Case Is = 9827
                    Range("A" & rng.Row).Characters(cnt, 1).Font.ColorIndex = 4
                Case Is = 9824
                    Range("A" & rng.Row).Characters(cnt, 1).Font.ColorIndex = 1
            End Select
        Next cnt
    Next rng
    Application.ScreenUpdating = True
End Sub

i fixed the colors myself ,Thanks alot man u are the best

 

[mumps]

You are very welcome.