Highlight the reverse patterns

[Kishan]

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
	A	B	C	D	E	F
1
2
3
4
5			P1	P2
6			X|1	2|1
7			X|X	X|1
8			2|X	1|1
9			X|1	X|X
10			1|X	X|1
11			X|2	X|1
12			1|1	1|1
13			1|2	1|2
14			X|1	1|X
15			1|2	2|1
16			1|1	1|X
17			1|1	1|X
18			1|1	1|1
19			1|1	1|1
20			1|2	2|1
21			1|1	X|1
22			1|X	2|X
23			X|1	X|X
24			1|X	X|1
25			1|X	1|1
26			1|2	1|1
27			X|1	1|1
28			2|X	X|1
29			X|2	1|1
30			1|X	X|1
31			X|X	1|1
32			2|X	1|1
33			1|X	X|1
34			1|1	1|1
35			X|1	1|1
36			1|1	X|X
37			1|2	2|1
38			1|1	X|1
39			1|2	2|1
40			1|1	1|1
41			2|1	1|X
42			1|1	1|1
43			1|1	1|1
44			1|2	1|1
45			1|2	2|1
46			1|1	1|1
47			1|1	X|X
48			1|1	1|X
49			1|1	1|X
50			1|X	1|X
51			1|X	X|1
52			1|X	1|1
53			X|1	X|2
54			1|2	X|1
55			1|X	X|2
56			X|2	1|1
57			1|1	1|1
58			1|1	1|2
59			1|X	X|X
60			1|1	1|1
61			1|X	X|2
62			1|1	X|1
63			1|1	2|X
64			X|X	1|1
65			2|X	X|2
66			X|1	1|1
67			2|1	1|2
68			2|2	X|X
69			1|1	1|1
70			X|2	2|X
71			2|1	2|1
72			2|X	2|2
73			1|X	X|1
74			2|X	1|1
75			2|1	1|1
76			X|2	1|2
77			2|1	1|X
78			X|1	1|2
79			1|2	1|2
80			2|X	X|1
81			1|1	1|1
82			1|2	1|2
83			X|1	X|X
84			1|2	2|1
85			X|2	1|2
86			X|2	1|X
87			1|1	X|1
88			1|2	2|1
89
90
91
92
Sheet1

Thank you in advance

Regards,
Kishan

 

[Rick Rothstein]

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)

 

[Kishan]

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>

 

[amitp]

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

 

[Kishan]

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>

 

[Rick Rothstein]

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.

 

[Kishan]

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>