Simple solution. Criteria based conditional formatting. (Index match) HELP!

Hey everyone,

I would like to ask for assistance in wrapping my head around a VBA macro or if possible inline formula. I'm sure its a fairly simple implementation, but I'm struggling with it seeing as I'm new to VBA.

I have two sheets that need to be compared and differences highlighted on budget sheet in the Budget sheet.

Please see the attached example sheet as it outlines my goal exactly.

Also please consider that these sheets will be ongoing, so more dates and vendors will be added.

Why do you use two different colours?
Why are columns H-O are not highlighted as these columns do not match?

I know that but you have not answered my questions.
Is it mistake to use 2 different colours? Is it also you have not coloured column H-O, though it should be coloured?

1. First, thanks so much for the fast reply! Really appreciate it!

Yes There should be multiple colors.

Basically, I would like the values between two sheets (Budget and Expense) to be conditionally highlighted on Budget.
-RED: Expense value > Budget value
-Green: Expense < Budget value
-YELLOW : Expense Value exists, but no value for Budget. or vice versa?
-No color: Budget=Expense.

Also note: Vender name is large in Expenses, but only a few are used in Budget. I am having trouble wrapping my head around the Match/Index of the vendors portion, to make sure the values correspond to the appropriate Vender /Date cell.

Even better option. To have colors dictate how much over or how much under?


2. Yes, H-O should be colored too. I uploaded a sheet before finishing those.

I think i'm looking at something similar to this with two way lookups (http://www.youtube.com/watch?v=rQQa1kfad0U) but it would have to be live and dynamic as I enter data in to both sheets. This seems a tough one

"-YELLOW : Expense Value exists, but no value for Budget. or vice versa?"

"Also note: Vender name is large in Expenses, but only a few are used in Budget"

If a cell does exist in expense but not in budget you can not colour in budget as it does not exist. This is only possible with match function if you colour the cells in expense sheet.

The clip deals with formulas and I do not think you can change the colour of a cell with a function.

To my understanding the process is something like this in 3 parts:

***Expenses=Sheet1 Budget=Sheet1

Part 1: Compare Vendors List

1. Lookup Sheet1's Long Vendor list.

2. Find matches for Sheet1's vendor list in Sheet2's smaller Vendor list.

3. Using vendor match's between Sheet1 and Sheet2, scan across row and match common months between the two common vendors.


Part 2: Compare Values for Vendors and Dates to find cells for comparision.

Part 3: Apply conditional formatting based on the cell's values (after all lookups)

Red: if Sheet1 cell value > Sheet2
Green: if Sheet1 cell value < Sheet2
Yellow: if Sheet1 cell value EXISTS, but Sheet2 Value does not exist
Blue: if Sheet1 cell value does not Exist, but Sheet3 Value Exists.
No Color: if Sheet1 cell value = Sheet2 Cell value

Part 4: All will persist if Vendor is added, or if more months are added.

It's really tough. Hopefully this made the mud a little clearer. To my understanding, this is possible to pull off inside excel? or is it better to make it a VBA code?

Can I show me how are going to return Name 17 from expense sheet by looking up in sheet budget using a Look up or match and index function?

Apologies, I should have mentioned this as well. The Expenses Sheet is somewhat of a master sheet that may have many vendors that will not be used in the budgeting sheet. I would like to account for this.

No ideas? Anyone?