Macro to compare the data between 2 files and generate the count

Hello Manish, The same can be achieved with formula solution also take a look
in I2 copy paste below
=IF(H2="","",COUNTIF($B$2:$B$8,H2))

in J2 copy paste below
=IF(I2="","",COUNTIF([Sample2.xlsx]Sheet1!$B$2:$B$6,H2))

*change the range criteria according to your actual data

in K2 copy paste below then hold control and shift together and hit enter and release all three keys
=IF(H2="","",MIN(IF(I2:J2>0,I2:J2)))

drag all the formulas down.

select I2 to K8 go to conditional formatting ---> select new rule--> add a formula---> type =len($K2)>0
in format select yellow color.