0

I need to match data in the column A's of two different sheets with similar data and then compare the values in column C. sheet 1 features data from this month, sheet 2 from last month.

I have column A of sheet 1 and column A of sheet 2 both have a lot of the same values, but in a different order.

Both column C's have numerical values. I'd like to indicate the change in numerical values by highlighting the cell in column C in red (decrease), green (increase), keeping the color the same if there is no change or if it's a new value.

I only need to color the newer sheet (sheet 1). See the image.

Can this be done without VBA (or with really simple VBA)?

comparison of two sample sheets

2

Yes you can use conditional formatting combined with a vlookup.

Select cells C2:C6 in sheet 1 click conditional formatting, goto new rule, select "Use a formula to determine which cell gets formatted. A formula to see if the sheet1 value is bigger would be.

=C2>VLOOKUP(A2,Sheet2!$A$2:$C$5,3,FALSE)

Then set your formatting below.

The way conditional formatting works this will update to =C3... or =C4... for the cells below the first one so they should all format correctly.

You can add multiple conditional formatting if you want to check for lower numbers as well.

  • VLOOKUP() is such a useful function! – Orun Jun 12 '14 at 19:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.