Excel 2007 : Highlight matching pairs-Excel 2007

Need some help for conditional formatting to locate matching pairs.
Bit difficult to explain so i've attached a spreadsheet with explanation.
Thanks in advance for any advice.
This is for excel 2007 but would be nice if it also worked in Excel 2010.

Hi Rob, welcome to the forum.

Select D1:E30 and create a new Conditional Formatting rule using the following formula:

=ISNUMBER(MATCH($D1&$E1,$H$1:$H$30&$I$1:$I$30,0))

Adjust the range $H$1:$H$30 and $I$1:$I$30 so that it encompasses all of your data in columns H & I.

Hope that helps!

Many thanks, the formula worked exactly as wanted.
Saved me an awful lot of brain-ache and studying the formula has given me an insight into aspects of excel I didn't know about.

Have just re-run the program and the conditional formatting for this doesn't come into effect automatically.

I have to go into 'manage rules' and 'apply' the conditional format before it highlights anything.

I also used your formula on single finds and it works well showing up straight away.

Other conditional formats show straight away as well but for some reason this one doesn't.

I have tried changing the 'order of preference' but still no show.

Any ideas....?

Thanks for your help.

Rob

post a workbook

try
=ISNUMBER(MATCH($D1&$E1,INDEX($H$1:$H$30&$I$1:$I$30,0),0)
instead

Hmmm...?

I thought conditional formatting treated all formulae as arrays, so why this should need re-entering on opening I don't know.
It seems to behave okay after it is re-entered

This work-around might help if nobody comes to the rescue.

In K1 (or any spare column, this can be hidden)

Please Login or Register  to view this content.

Enter with Ctrl+Shift+Enter
Drag/Fill Down to last row of D:E

Formula is:=

Please Login or Register  to view this content.

Applies to:=

Please Login or Register  to view this content.

Hope this helps, I'll dig deeper into this later, but better if someone else can explain this.

[EDIT]
That solves it Martin although I think you have missed a closing bracket.

=ISNUMBER(MATCH($D1&$E1,INDEX($H$1:$H$30&$I$1:$I$30,0),0))

I can confirm the behaviour is specific to XL2007 in so far as XL2010 behaves correctly (I would presume XL2003 to work correctly also but can not confirm at present)

We've noticed a few Array-related calculation bugs in XL2007 were fixed in a recent HotFix, however, it would seem that this particular issue appears to have eluded MS ... good catch.

Hi Don, thanks for pitching in.

I thought I was going nuts, I tried everything I could think of and still no good, including this VBa to generate the conditional formatting on opening.

Please Login or Register  to view this content.

I toggled the commented out lines to test the code and called the code through the Workbook Open event, and the Workbook SheetActivate, etc but no good.

The file with the offending formula opens fine in 2003, now here's the rub, if the .xlsx is saved in 2003 as a .xls in opens okay in 2007! (without any code or modification in 2003).

I've attached the 2003 file.

There might be other work-a-rounds but I can't look just now, I'm brain-dead with the combinations I've tried.

Thanks Martin, worked a treat and I forgive you for the missing bracket..lol....thanks also to others who pitched in.

I have a further addition I need (want) to make to this formula (if the problem can be included in the formula which I'm sure it can) and will post with a workbook later If someone can point me in the right direction. Although looking at the way you guys help out I think a complete result is more the order of the day.....Excellent forum, by the way, glad I found it....

Mod.: new thread: http://www.excelforum.com/excel-2007...ing-pairs.html