Compare + Conditional Formatting Help

[omniscience]

Hey, everyone I am having difficulty with something that I'm sure is simple enough to put together. Politely asking for some assistance or direction.

I am using Excel 2010.

I have 2 sheets set up as follows:

-Vendors in Column A.
-Months of the Year in Row 2. (this will be ongoing, likely spanning many years)
-Currency Values are in the table. (some vendor salaries are paid multiple months)

What I would like to do:

The vendor lists from both sheets are of different length. Sheet 1 has expenses. Sheet 2 has the estimated budget.

I assume the macro would have to do this all in one step:

Part 1:

I need to compare cell values based on:
Compare the two vendors columns (Sheet 2 Vendors list is smaller). Find matches. Scan the row and find the cell that has the same column month. Compare the cells.

Part 2:

I would need some conditional formatting based on the comparisons.
Something like this:
-If the values are the same. = Green.
-If one is greater than the other = Red.
-If one is less than the other = Blue.

I would greatly appreciate any help anyone could provide. I have attached a sheet with an example. I tried to fill it out the best I could. Please see Sheet1 (the lookup sheet) Sheet2 = the sheet that needs the markup and highlighting.

[Bob Phillips]

I would change the month headings to real dates with a formula of =DATE(2012,COLUMN()-COLUMN($A$1),1) copied across, on both Expenses and Budget and format the cells as mmm.

Then the CF formulae are:
Red: =AND(B4="",INDEX(Expense!$A$1:$Y$21,MATCH($A4,Expense!$A:$A,0),MATCH(B$2,Expense!$2:$ 2,0))>0)
Green: =INDEX(Expense!$A$1:$Y$21,MATCH($A4,Expense!$A:$A,0),MATCH(B$2,Expense!$2:$ 2,0))<B4
Yellow: =INDEX(Expense!$A$1:$Y$21,MATCH($A4,Expense!$A:$A,0),MATCH(B$2,Expense!$2:$ 2,0))>B4

[omniscience]

Would this be dynamic? If I continuously added values, this would carry on?

[Bob Phillips]

Add values where?

[omniscience]

Add values where?

If I added more vendors, and then inputted costs into the sheets. There will be many more numbers populating the tables than the example I have outlined.

Is it possible for you to upload the file with your implementation? I understand what is doing on, but I'm getting a worksheet referencing error?

Also many thanks for your very quick reply!

[Bob Phillips]

If the tables are likely to grow, it would be best to build some dynamic named ranges and use them in the formulae.

I will build an example and upload it later tonight/tomorrow.

[omniscience]

Thank you kindly! I would greatly appreciate it if you could. I am curious as well. Would there be any advantage generating this all automatically within VBA?

[omniscience]

If the tables are likely to grow, it would be best to build some dynamic named ranges and use them in the formulae.

I will build an example and upload it later tonight/tomorrow.

Hey xld,

Were you able come up with an example? I would really appreciate taking a look at your iteration.