Need help with changing VBA code

[Schturman]

Hi to all.
Please help me change/improve this code that will colorize not only the symbol "!", but also the name before symbol "!" till comma that comes before the name ? (see pic)
Maybe it will help - before each name has Space, also if the name is the first word in line, same if name comes after symbol of House.
Some names has also a letter in the brackets like (ת) or (ל), it's also should be colorized.
The range of searching is E5:E35.
This is a code:
=====================
Sub paintText()

Set myRange = Range("E5:E35") 'The Range that contains the substring you want to change color
substr = "!" 'The text you want to change color
txtColor = 3 'The ColorIndex which repsents the color you want to change

For Each myString In myRange
lenstr = Len(myString)
lensubstr = Len(substr)
For i = 1 To lenstr
tempString = Mid(myString, i, lensubstr)
If tempString = substr Then
myString.Characters(Start:=i, Length:=lensubstr).Font.ColorIndex = txtColor
End If
Next i
Next myString

End Sub
=====================

Thanks

 

[shknbk2]

I think this will get you to change name color from ! to previous comma or beginning of line. I'm not sure what to do with the house symbol, but it might be determining its ascii code and using the commented-out If line.

Sub paintText()
    Dim myRange As Range
    Dim substr As String
    Dim tempString As String
    Dim txtcolor As Long
    Dim myString As Range
    Dim i As Integer, j As Integer
    Dim lenstr As Integer
    Dim lensubstr As Integer
   
    Set myRange = Range("E5:E35") 'The Range that contains the substring you want to change color
    substr = "!" 'The text you want to change color
    txtcolor = 3 'The ColorIndex which repsents the color you want to change
   
    For Each myString In myRange
        lenstr = Len(myString)
        lensubstr = Len(substr)
        For i = 1 To lenstr
            tempString = Mid(myString, i, lensubstr)
            If tempString = substr Then
                For j = i - 1 To 1 Step -1
                    tempString = Mid(myString, j, 1)
                   
                    'If tempString = "," Or Asc(tempString) = ## Then
                    If tempString = "," Then
                        myString.Characters(Start:=j + 1, Length:=i - j).Font.ColorIndex = txtcolor
                        Exit For
                    ElseIf j = 1 Then
                        myString.Characters(Start:=1, Length:=i).Font.ColorIndex = txtcolor
                        Exit For
                    End If
                Next
            End If
        Next i
    Next myString

End Sub

 

[Schturman]

Thank you !
It almost done. It have 2 problems:
1. It also colorize the Hose icon and name that came before it (see pic)
2. If first name colorized and for example I change this name or remove symbol "!", it colorize all line instead to restore the previous color and I need manually restore to the Blue color

If it help, I can change the House icon to this "##" symbol.

 

[Schturman]

I don't know why, but I can't edit my posts on this forum...
I want to say, if it will help, I can also add the another symbol to the start of line. For example like in pic:

 

[shknbk2]

Adding the flag to the beginning doesn't change anything except it would also change to red. The problem with the house symbol is that I don't know how to find it in the text. Therefore, I put in the commented line to consider its ascii value. I don't have hebrew, and I can't find the symbol online. If you can figure out what its ascii number is, you can use the commented line instead of the If line that just finds the comma only.

The "##" in the commented line was not meant to be the symbol to use. It was meant to indicate the ascii table numbers of the house symbol. To find it, you can put the house symbol as the only character in a cell and then in the Immediate Window (Ctrl + G) in the VBE, type this (including the ?) and hit enter:

?Asc(activecell.Value)

For example, if the character in the cell is the English "A", the answer will be 65. This all assumes that the house is a character with an ascii value. If it is a picture or something else, we will have to come up with a different solution.

For the all red/blue issue, maybe the code can change all color to blue at the beginning of the code and then re-red the appropriate text.

 

[Schturman]

Oh sorry, It was my mistake... I missed your explanation about ASCII code.
You code working PERFECTLY !!!
I changed the House icon to another symbol + added to beginning of line symbol "$" and activated the commented line from your code

If tempString = "," Or tempString = "^" Or tempString = "$" Then

It work just perfectly !!!!
Now I also tried another icons, it not have too much icons with ascii code, but what I tried (for example this symbol ©) also work perfectly:

If tempString = "," Or Asc(tempString) = 169 Or tempString = "$" Then

But for example if I use Symbol (Hex) code (see pic), it not work. I understand this line (Or Asc(tempString) = 169) should be changed in this case..

I will continue to play with this.
Thank you very much !!!!

 

[Schturman]

Wow, I'm so happy ! Look, it work perfectly !

But I want ask you a few questions, please:
1. Like you can see I changed the icon of House to icon of Church () and in the beginning of line the Flash icon () instead of Flag
I used your option - ?Asc(activecell.Value) - to found a Ascii code of Home and Flag icons and a result was 63 for both.
Here I don't understand why for different icons I get the same code ?

2. I found that I can use also a Unicode of icons by writing:

tempString = ChrW(&H26EA) 'The Unicode of Church is 26EA
tempString = ChrW(&H26A1) 'The Unicode of Flash is 26A1

I found a real Unicode of House icon (1F3E0) and for Flag icon (1F3F4), but like you can see it have 5 characters and it not work if I add it like before:

tempString = ChrW(&H1F3E0)

Can you explain why, please ?
Thanks

 

[shknbk2]

1. Use 'AscW' insead. The range for 'Asc' is 0-255, while the range for 'AscW' is -32768–65535. It will give you the decimal equivalents (&H26EA = 9,962)
2. For the same reason, the range -32768–65535 for ChrW isn't big enough for &H1F3E0, which is 127,968 in decimal.

 

[Schturman]

1. Use 'AscW' insead. The range for 'Asc' is 0-255, while the range for 'AscW' is -32768–65535. It will give you the decimal equivalents (&H26EA = 9,962)
2. For the same reason, the range -32768–65535 for ChrW isn't big enough for &H1F3E0, which is 127,968 in decimal.

Thanks for answer!
1.
a) If use ?Asc(activecell.Value) - I get the "63" for both icons (Home = 1F3E0 & Flag = 1F3F4)
b) If I use ?AscW(activecell.Value) - I get the "-10180" for both icons (Home = 1F3E0 & Flag = 1F3F4)
I don't understand why for both icons I get the same code ?

2.
a) If I want to colorize only this one Home icon, for example I use the my first variation code (instead "!" will colorize Home icon).

substr = ChrW(-10180)

For some reasons it convert Home icon for 2 squares and colorize the first one (see pic)

b) If I use substr = ChrW(63) or substr = Chr(63) or substr = AscW(63) or substr = Asc(63) or substr = Asc(-10180) or substr = AscW(-10180)
It not colorize at all...

Maybe I write it incorrectly ?
Thanks

 

[MARK858]

I don't know why, but I can't edit my posts on this forum...

New members can't edit posts until they made a certain number of posts (anti spam measure), after that you will be able to edit your post for 10 minutes after your posting.