Conditional formatting and vlookup

[Tej92]

Hello everyone, I'm trying to have conditional formatting to work for a large data set, i've got 70 areas and 52 weeks to look for.
here is a shorter version of the data that I'm working with

area ↓ week->	2	4	6	8	10
Area 1 apple
Area 2 berry
Area 3 orange
Area 4 banana

I have multiple worksheets with the same name as the first two words from the area column like this: Area 1_data, Area 2_data, etc..
i would like conditional formatting to look for the week number in the worksheet that has the first two words and "_data" in the column D and if it finds the number "2" for example it colors the cell green otherwise nothing

is it possible without having to do it all manually?

thank you in advance for looking at it

 

[kweaver]

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow others to copy/paste it to an Excel spreadsheet, so someone can work with the same copy of data that you are using. Instructions on using this tool can be found here: XL2BB Add-in

 

[Tej92]

Hi @kweaver, thanks for the reply.
I'm on a company computer and not allowed to download add-ons.
I'm really sorry, is there anything else that I can provide to make this easier?

 

[Tej92]

up

 

[kevin9999]

Could you share your file via Dropbox, Google Drive or similar file sharing platform?

 

[pjmorris]

it sounds like it is entirely possible. When you say look in the sheet named 'Area 1_data' for example what is the structure of the data in 'Area 1_data'. Also what happens if there are multiple occurences of Area 1, for example 'Area 1 Apple' and 'Area 1 Berry'?

 

[Tej92]

I tried installing XL2BB but not allowed on this computer.
Managed to sort this issue some other way not the most efficient but works for now.
Thanks everyone for looking into the matter.