Highlight the reverse patterns

Kishan

Well-known Member
Joined
Mar 15, 2011
Messages
1,645
Office Version
  1. 2010
Platform
  1. Windows
Using Excel 2000

Hi,

I want a Formula or may be VBA to "highlight the reverse patterns", look through column C & highlight if found reverse pattern in column D.
For examples
C10 is 1|X & D10 has X|1
C12 is 1|1 & D12 has 1|1
C14 is X|1 & D14 has 1|X
C15 is 1|2 & D15 has 2|1 and so on....

Result data example


Book1
ABCDEF
1
2
3
4
5P1P2
6X|12|1
7X|XX|1
82|X1|1
9X|1X|X
101|XX|1
11X|2X|1
121|11|1
131|21|2
14X|11|X
151|22|1
161|11|X
171|11|X
181|11|1
191|11|1
201|22|1
211|1X|1
221|X2|X
23X|1X|X
241|XX|1
251|X1|1
261|21|1
27X|11|1
282|XX|1
29X|21|1
301|XX|1
31X|X1|1
322|X1|1
331|XX|1
341|11|1
35X|11|1
361|1X|X
371|22|1
381|1X|1
391|22|1
401|11|1
412|11|X
421|11|1
431|11|1
441|21|1
451|22|1
461|11|1
471|1X|X
481|11|X
491|11|X
501|X1|X
511|XX|1
521|X1|1
53X|1X|2
541|2X|1
551|XX|2
56X|21|1
571|11|1
581|11|2
591|XX|X
601|11|1
611|XX|2
621|1X|1
631|12|X
64X|X1|1
652|XX|2
66X|11|1
672|11|2
682|2X|X
691|11|1
70X|22|X
712|12|1
722|X2|2
731|XX|1
742|X1|1
752|11|1
76X|21|2
772|11|X
78X|11|2
791|21|2
802|XX|1
811|11|1
821|21|2
83X|1X|X
841|22|1
85X|21|2
86X|21|X
871|1X|1
881|22|1
89
90
91
92
Sheet1


Thank you in advance

Regards,
Kishan
 
Last edited:

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
I wasn't sure exactly what you meant by "highlight", but you did ask for a formula so... this formula will return TRUE if the contents of Columns C and D are reverses of each other on a given row and FALSE otherwise. Since your data starts on Row 6 put this formula in cell E6 and copy it down...

=C6=MID(D6&"|"&D6,3,3)
 
Last edited:
Upvote 0
I wasn't sure exactly what you meant by "highlight", but you did ask for a formula so... this formula will return TRUE if the contents of Columns C and D are reverses of each other on a given row and FALSE otherwise. Since your data starts on Row 6 put this formula in cell E6 and copy it down...

=C6=MID(D6&"|"&D6,3,3)
Hi Rick Rothstein, I applied your given formula first CF in column C & then in Column D, it worked fine highlights the both rows with reverse match correctly</SPAN></SPAN>

Thank you for your help
</SPAN></SPAN>

Regards,
</SPAN></SPAN>
Kishan
</SPAN></SPAN>
 
Upvote 0
Hi Kishan,

you can also use below custom functions
and copy the below formula in Cell E6 (then drag it down)
It will highlight the matching cells

=IF(stringrev(C6)=D6,rhighlt(C6:D6,C6:D6),"")
it will highlight the cells in red font wherever the match is true

'1. Reversal of string
Function STRINGREV(Mystring As String)


STRINGREV = StrReverse(Mystring)


End Function

'2. highlight a cell range in a range
Function rhighlt(Val As Range, ByVal tablearray As Range)


Dim rngV As Range
Dim rngS As Range


Set rngV = Val
Set rngS = tablearray




For Each rngV In Val.Cells
For Each rngS In tablearray.Cells
If rngV.Value = rngS.Value Then
rngV.Characters(1, Len(rngV.Value)).Font.Color = RGB(255, 0, 0)
End If
Next rngS
Next rngV


rhighlt = ""


End Function
 
Upvote 0
Hi Kishan,

you can also use below custom functions
and copy the below formula in Cell E6 (then drag it down)
It will highlight the matching cells

=IF(stringrev(C6)=D6,rhighlt(C6:D6,C6:D6),"")
it will highlight the cells in red font wherever the match is true
Hi amitp, I did as you said but can't see it highlights any match cells in red font , don't know what I am doing wrong, is there any VBA solution for this</SPAN></SPAN>

Thank you for your help
</SPAN></SPAN>

Regards,
</SPAN></SPAN>
Kishan
</SPAN></SPAN>
 
Last edited:
Upvote 0
Hi amitp, I did as you said but cant see highlights any cells

Did you want the cell and its reverse highlighted like in Message #1 ? If so, you can use Conditional Formatting to get that. Select cell C6, then scroll down to the bottom of your data and SHIFT-click on the last data item in Column D... this will select all of your data cells keeping cell C6 as the active cell. Now call up the Conditional Formatting dialog box, choose New Rule (or its equivalent in your version of Excel) and choose the option that lets you put a formula in as the condition, then put this formula (a slight modification of the formula I posted earlier) in the field that the formula goes in...

=$C6=MID($D6&"|"&$D6,3,3)

Use whatever option lets you select the format to use and select the Fill tab and pick the fill color to use. Now OK your way back to the worksheet. All of the values and their reverses should now be highlighted.
 
Last edited:
Upvote 0
Did you want the cell and its reverse highlighted like in Message #1 ?
Yes Rick Rothstein, you got it correct; this is doing perfect job what I wanted, and thank for explanation who to use it. </SPAN></SPAN>

Thank you for your help</SPAN></SPAN>

Regards,</SPAN></SPAN>
Kishan </SPAN></SPAN>
:)</SPAN></SPAN>
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top