Colorizing partial value of a range - Weird behavior! (1 Viewer)

[Isaac]

Anyone who can help me with this gets a gold star! So I have this code that works perfectly - on some cells - to colorize parts of the cell. On other cells, when you run the code all together, it incorrectly colorizes the ENTIRE cell (rather than just the words that I expect it to).

Frustratingly enough, if I step through the code line by line, it works fine. The If Instr(..... portion recognizes what it's supposed to and it skips over the words it's supposed to.

The logic is simple: For every cell in A4 and below: for every chunk of text (array split by a space), see if that text exists in A1. If it does, colorize it red.

Works perfectly if I step thru it line by line. Doesn't work on, for example, line 13 or 48, if run all at once. I hope I have not hit up against something unsolvable!! File attached

 

[vba_php]

i don't really have an answer, but I will say like I've said so many times before, that VBA isn't the best and it messes up all the time, and those times it cannot be explained. one possible change, which I have no idea would make any difference, would be to change this:

Set rngList = Sheet1.Range("a4:a" & lastrow)

to this:

dim rngCell As Range

For Each rngCell In range("a4", range("a4").end(xldown))

the problem could be anything, as far as what I've experienced with Excel. but then again, you are doing something that is not really common. I've never seen anyone post a question like this, on any forum, regarding coloring words inside of cell by way of a coding solution.

the other guess I would have is that the issue is happening because a coding solution is not normally utilized in this manner, in terms of "parsing text inside of cells". a related issue I've had with excel is parsing text out inside of cells, throwing them into an array like you are doing, and having them come out as the same values. and of course, that's weird also, which makes no sense.

what happens if you close excel completely open it again by way of a doubt click on the EXE, and then run the code again for the first time? does it work under that scenario?

 

[isladogs]

I ran your ColorizeMatches code and got the following result

Records in rows 4, 13 & 48 are completely red as you stated.
But there are other peculiar results e.g. Look at 'Granting' in Row 14.

Adding random text to row 13 had no effect, it all remained red.
Stepping through the code made no difference as far as I could see

 

[Isaac]

weird, when I stepped thru it, things like row 13 were totally solved. I set up a Watch where rngCell.row=13 and then stepped through it - worked perfectly. "Granting" - yeah I have less than no explanation for that. This bites especially when so many of them work correctly!

 

[vba_php]

i also ran your stuff, and I got the same thing as colin did hours ago:

 

[theDBguy]

I know nothing about Excel programming, so as usual, I cheated.

 

[Darrell]

Row 16 - You have 2 spaces after "FDIC:" and your code is getting tripped up by it. Was working fine until it got to that part.
Row 48 - There is a redundant space at the end of the sentence that is tripping it up.

 

[arnelgp]

Sub agpColorizeMatches()
'on error goto errhandler
Application.EnableEvents = False
Application.ScreenUpdating = False
Sheet1.Unprotect "Mufg1234"
Dim lastrow As Long
lastrow = Sheet1.Range("A" & Sheet1.Rows.Count).End(xlUp).Row
Dim rngList As Range, rngCell As Range
Set rngList = Sheet1.Range("a4:a" & lastrow)
Dim arrWords() As String, lngStartPosition As Long, x As Long, lngWordLength As Long
Dim wrd As Variant
Dim wordCol As New Collection
arrWords = Split(Sheet1.Range("A1").Value, " ")
For Each wrd In arrWords
    If Len(Trim(wrd & "")) > 0 Then
        wordCol.Add Trim(wrd & "")
    End If
Next

For Each rngCell In rngList 'go down the list of Sent items
    For x = 1 To wordCol.Count
        lngStartPosition = InStr(1, rngCell.Value, wordCol(x))
        If lngStartPosition > 0 Then
            lngWordLength = Len(wordCol(x))
            With rngCell.Characters(Start:=lngStartPosition, Length:=lngWordLength).Font
                .Color = -16776961
            End With
        End If
    Next x
Next rngCell
Set wordCol = Nothing
Application.EnableEvents = True
Application.ScreenUpdating = False

Exit Sub
errhandler:
MsgBox "An error has occurred in ColorizeMatches procedure:  " & vbNewLine & vbNewLine & Err.Description, vbCritical, "Covid 19 List"
Sheet1.Protect "Mufg1234"
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

 

[Isaac]

arnelgp, thanks for taking the time on that. it worked well the first time I ran it, but just like before it seems haunted....I then put the phrase "financial" in the box, and nothing - no word anywhere - was colorized. I bet if I step through it line by line slowly, it will work fine just like before. I might just switch to another approach...I think my requirements may be flexible here.

 

[arnelgp]

open the workbook.
click "Enable Content"
on the Ribbon->Home, @ the rightmost, there is a Button (Colorize Text).

 

[isladogs]

@arnelgp
That's very neat and surprisingly quick.
I've not yet studied your code.
Out of interest, how difficult would it be to limit the colourising to whole words that match

 

[arnelgp]

not that hard.
you just loop the instr() function until no search match is found.

 

[Isaac]

anelgp, is it using the same code that you had posted before? I had already tried it, and it was just like my code - worked sometimes, didnt' work sometimes.