match duplicate pair value ONLY between 2 columns using conditional formatting

I have values in 2 columns, column D & column H

If any value in column D is identical to value in column H, then I want to highlight these 2 matching cells.

I have tried conditional formatting but having problem as described below

1. On attached spreadsheet there are 30,000 in H43, H44, H45 and they are highlighted. I don't want them to be highlighted because there is no 30000 in Column D

2. On attached spreadsheet there are two 1000(D41 & D42) in Column D and three 1000 (H39, H40, & H41). All of these five 1000 are highlighted. I want only 4 of them highlighted (D41 against H39, and D42 against H40) leaving H41 aside because it doesn't have pair on column D.

I would greatly appreciate your assistance to get this issue sorted.DaybookRec.xlsm

Hi, use these conditional format formulas:

in D5:D46 with D5 active apply:
=AND(D5<>"",COUNTIF(H$5:H$46,D5)-COUNTIF(D$4:D4,D5)>=1)

in H5:H46 with H5 active apply:
=AND(H5<>"",COUNTIF(D$5:D$46,H5)-COUNTIF(H$4:H4,H5)>=1)

Hi ragulduy,

Thanks for the formula and I tried a lot applying but not giving desired result. is there any other way to match and high light pair value between 2 columns.

Hi - please take a look at the attachment and let me know which part is not desired and I will adjust as neccessary.

Thank you raguldy. It works perfectly. You are genius.

My apologies, it was may fault that i was applying formula directly on the cell rather on "conditional formatting " box, hence was not working as desired.

Now I have to spend time on it to understand how this countif formula works here.

wish you a wonderful weekend.