String Challenge- Colour common characters

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

String Challenge- Colour common characters

Post by Rudi »

Hi,

This is more a challenge than a genuine request, so don't put time in it if you have better things to do.

Is there a way to loop through a range and test each character in each cell and highlight it only if it is duplicated in all the other cells in the range.
BTW: Could the scripting dictionary be useful in this scenario?

TX

See the sample workbook.
Highlight common chars.xlsm
You do not have the required permissions to view the files attached to this post.
Last edited by Rudi on 13 Jul 2014, 17:37, edited 1 time in total.
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: String Challenge

Post by HansV »

Here is a macro that uses only native Excel functionality:

Code: Select all

Sub HighlightCommon()
    Dim rngAll As Range
    Dim rngCell As Range
    Dim strVal As String
    Dim strChar As String
    Dim lngCount As Long
    Dim i As Long
    Dim j As Long
    Set rngAll = Selection ' or whatever you want
    lngCount = rngAll.Count
    Set rngCell = rngAll.Cells(1)
    strVal = rngCell.Value
    For i = 1 To Len(strVal)
        strChar = CStr(Mid(strVal, i, 1))
        If Application.WorksheetFunction.CountIf(rngAll, "*" & strChar & "*") = lngCount Then
            For Each rngCell In rngAll
                For j = 1 To Len(rngCell.Value)
                    If rngCell.Characters(j, 1).Text = strChar Then
                        rngCell.Characters(j, 1).Font.Color = vbRed
                    End If
                Next j
            Next rngCell
        End If
    Next i
End Sub
This version operates on the selected range, but you can easily modify it if you want something else
Best wishes,
Hans

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: String Challenge

Post by HansV »

A slightly generalized version that can be called in different ways. It also resets the font color before highlighting characters.

Code: Select all

Sub HighlightCommon(rngAll As Range)
    Dim rngCell As Range
    Dim strVal As String
    Dim strChar As String
    Dim lngCount As Long
    Dim i As Long
    Dim j As Long
    ' Optional - reset font color
    rngAll.Font.ColorIndex = xlColorIndexAutomatic
    lngCount = rngAll.Count
    Set rngCell = rngAll.Cells(1)
    strVal = rngCell.Value
    For i = 1 To Len(strVal)
        strChar = CStr(Mid(strVal, i, 1))
        If Application.WorksheetFunction.CountIf(rngAll, "*" & strChar & "*") = lngCount Then
            For Each rngCell In rngAll
                For j = 1 To Len(rngCell.Value)
                    If rngCell.Characters(j, 1).Text = strChar Then
                        rngCell.Characters(j, 1).Font.Color = vbRed
                    End If
                Next j
            Next rngCell
        End If
    Next i
End Sub
[code]
Two examples of using it:

Sub HighlightCommonSelection()
    ' Apply to the currently selected range
    Call HighlightCommon(Selection)
End Sub

Sub HighlightCommonSpecific()
    ' Apply to a specific range
    Call HighlightCommon(Worksheets("Sheet1").Range("A2:A5"))
End Sub
Note: if there are any blank cells within the range, no characters will be highlighted, obviously. If you want the code to ignore blank cells, it will have to be modified.
Best wishes,
Hans

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: String Challenge

Post by Rudi »

Hi,

Wow! I had to read through this code a dozen times to try and visualize that you were doing!!

The hardest part for me when I was trying to plan this was to determine which looping action had to be on the outside and how to order subsequent inner loops. I see you take advantage of the CountIf function too which was something I did not even think of while my mind was so tunnel-visioned on the loops!

There is something about the CountIf I don't understand. You are counting the occurrence of the character in the range, but why is the condition of the IF = to the count of cells in the range? I don't quite understand the purpose of this?

Another thing to wrap my mind around is that you count the string length twice:
Here: For i = 1 To Len(strVal)
and then
Here: For j = 1 To Len(rngCell.Value)

Please don't go into too much detail, but can you briefly clarify these two scenarios I raise.

TX for the code. It is a great learning curve, and thanks too for your time.
Cheers

I think you need a :chocciebar: and some :wine: for this!!
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: String Challenge

Post by HansV »

The code loops through the characters in the first cell in the range - if a character is to occur in all cells of the range, it MUST occur in the first cell, of course. There is no need to look at other characters.
If a character C occurs in all cells of the range, COUNTIF(range, "*C*") must be equal to the number of cells in the range. Or, stated differently if COUNTIF is less than the number of cells, the character doesn't occur in all cells.
In the inner loop, we loop through all cells in the range to highlight a character. In this inner loop, rngCell is a different cell each time, it's not the first cell any more. I could have declared a separate variable for this, but I reused the variable rngCell instead.
Best wishes,
Hans

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: String Challenge

Post by Rudi »

HansV wrote:If a character C occurs in all cells of the range, COUNTIF(range, "*C*") must be equal to the number of cells in the range.
TX for the info.
What got me on the CountIF is that you said it must equal the cell count. What happens if *C* is greater than the cell count? In other words the letter C can occur a few times in the same cell so the overall count would be greater than the cell count and make the statement false.

Sorry this is the only other unclear issue.
TX
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: String Challenge

Post by HansV »

COUNTIF counts the number of cells that satisfy a condition, not the total number of occurrences within those cells.
Best wishes,
Hans

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: String Challenge

Post by Rudi »

Oops... silly me...
It's weekend! I'm allowed to have a dumb blonde moment!! :grin:
TX for all!!
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.