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.
String Challenge- Colour common characters
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
String Challenge- Colour common characters
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.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Administrator
- Posts: 78236
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: String Challenge
Here is a macro that uses only native Excel functionality:
This version operates on the selected range, but you can easily modify it if you want something else
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
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 78236
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: String Challenge
A slightly generalized version that can be called in different ways. It also resets the font color before highlighting characters.
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.
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
Best wishes,
Hans
Hans
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: String Challenge
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 and some for this!!
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 and some for this!!
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Administrator
- Posts: 78236
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: String Challenge
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.
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
Hans
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: String Challenge
TX for the info.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.
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.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Administrator
- Posts: 78236
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: String Challenge
COUNTIF counts the number of cells that satisfy a condition, not the total number of occurrences within those cells.
Best wishes,
Hans
Hans
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: String Challenge
Oops... silly me...
It's weekend! I'm allowed to have a dumb blonde moment!!
TX for all!!
It's weekend! I'm allowed to have a dumb blonde moment!!
TX for all!!
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.