I can't think of anyway to do this without code.
Here's a quick loop that highlights all cells yellow in the range that match the same pattern.
To use this, simply go to Excel, hit Alt+F11, Insert->Module, and paste the code below. Be sure to change the two ranges as notated below. The first range holds the cell you want to compare, and the second range contains all the cells you want to search. Then close that window.
You can call the macro from normal spreadsheet world with Alt+F8.
Sub patterns()
Dim strCompare As String, strSearch As String
Dim i As Integer, q As Integer
Dim cell As Range, compare As Range
Set compare = Range("a1")
q = Len(strCompare)
For i = 1 To q
If Mid(strCompare, i, 1) = "a" Or _
Mid(strCompare, i, 1) = "e" Or _
Mid(strCompare, i, 1) = "i" Or _
Mid(strCompare, i, 1) = "o" Or _
Mid(strCompare, i, 1) = "u" Then
strCompare = strCompare & 1
Else: strCompare = strCompare & 2
End If
Next
For Each cell In Range("a2:f50")
q = Len(cell)
For i = 1 To q
If Mid(cell, i, 1) = "a" Or _
Mid(cell, i, 1) = "e" Or _
Mid(cell, i, 1) = "i" Or _
Mid(cell, i, 1) = "o" Or _
Mid(cell, i, 1) = "u" Then
strSearch = strSearch & 1
Else: strSearch = strSearch & 2
End If
Next
If strCompare = strSearch Then cell.Interior.Color = 65535
strSearch = ""
Next
End Sub
Bookmarks