Lookup and color

[Kamal Thakur]

Hi All,
I need some help in building a macro.
I have a spreadsheet which has more than 100 tabs in it (attached as template 1 which has 3 tabs as sample)
One more spreadsheet (attached as template 2) has unique numbers in Column E
I need a macro which should search the unique number in template 1 in column F and if found highlight that row from Cell A to Cell I and put a tick mark in column J.(shown in template 1)
This unique number could be any tab and could repeat multiple times
All your help is much appreciated as this could save my lot of manual work
Thank you.

 

[Debraj]

Hi Kamal,

I am lil bit confused..
Being the only one of its kind; unlike anything else

Please clarify the logic
in Template 1>
Tab 1. J7 was marked as Tick.. why!!
In Tab 2. J5 was not marked.. why!!

In Template 2>
Do we need to do anything with template 2 > sheet1

 

[SirJB7]

Hi, Kamal Thakur!

You could go for the macro solution or you can try this one time formula solution:
a) Select all the tabs (click on 1st one, move to last, hold shift and click on it)
b) In cell J4 type this formula:
=SI(F4="";"";SI(NO(ESNOD(BUSCARV(F4;'[Template 2.xlsx]Sheet1'!$E:$E;1;FALSO)));"a";"")) -----> in english: =IF(F4="","";IF(NOT(ISNA(VLOOKUP(F4,'[Template 2.xlsx]Sheet1'!$E:$E,1,FALSE))),"a",""))
c) Copy down as required (taking care of reaching a row greater or equal to the tab with the highest number of non empty rows)

Regards!

 

[Kamal Thakur]

Hi deb

I missed the check mark by mistake

 

[Kamal Thakur]

But as I said it should color and put the right tick mark

Your help is much appreciated

 

[Kamal Thakur]

Hi deb any help on this request

 

[NARAYANK991]

Hi Thakur ,

Are you very keen that the requirement should be met through a macro ?

The formula given by SirJB7 , and Conditional Formatting can do the job ; the only drawback being that you have to manually put in the formula and the CF in each of the 100 sheet tabs. If this is not acceptable , then a macro is the only way.

You can check your file here , and see if it is acceptable.

Narayan

 

[Kamal Thakur]

Hi Narayan,

Yes I would need a macro bcoz as I ssaid earlier In template 2 the data changes every week.

Coming back to Template 1 I update all the fields till column I manually, however when I execute the macro my requirement.

I can use the formula however I have a problem lets say the value in column F could be repetative for example it scan in cell F26 and F150 on tab 1 which i think will create a problem.

So It would be great if there is a macro it can solve my problem.

You have attached one excel spreadsheet, does it have any macro?

Thanks for all your help

Kamal

 

[NARAYANK991]

Hi Kamal ,

The file I have attached does not have any macros.

If you are looking for a macro , I will take some time to upload a file with the macro.

Narayan

 

[Kamal Thakur]

Thank you Narayan, I will be waiting for your next response