Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I need to check if every cell value in single column in wookbook 1 is contained in a column in workbook 2, then check the value of another related column in workbook 2, and if its "Success" then highlight the original cell value in the workbook 1.

I'm an absolute beginner at Excel 2007 so any advice is appreciated!

R

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

Well, quite a task for an absolute beginner, but here you go:

In Workbook 1, create an additional column to check your condition. This column can be on a seperate worksheet, if you like.

A is your single column of workbook 1. B is your condition-check column.

B1=MATCH(A1,[Workbook2.xlsm]Sheet1!C:C,0)

Not sure, if I understood you right, but if you have to do this check with two related columns:

B1=AND(MATCH(A1,[Workbook2.xlsm]Sheet1!C:C,0),MATCH(A1,[Workbook2.xlsm]Sheet1!D:D,0))

Then you need to setup conditional formatting for column A of Workbook 1 to create the dynamic highlighting.

Select the whole column A, then add an conditional format based on a formula. The formula you can use is =B1. When you lookup this conditional format the range which it is used on should be $A:$A.

That should be it.

share|improve this answer
    
Thanks for your help and the detailed description... I think may take some time to figure out! The second check, the "success check" I mention is a boolean check, but with it being the text "Success" to mean true. It doesn't relate to the first sheet. –  user1815997 Nov 12 '12 at 15:07
    
ah, ok, this would be then just something like (A1="SUCCESS"), BUT, A1 would need to be your related cell of the related column from worksheet2. I don't know the relation, so I can't provide you a more detailed answer. –  Jook Nov 12 '12 at 15:21
    
I've solved my problem in a different way in the end. Your code was very useful as it lead me to a web page that had some VBA macro code that was close enough for me to change into doing what I needed. Ultimately a different approach but the outcome is the same. –  user1815997 Nov 12 '12 at 16:26
    
ok, no problem :) but next time, if you'd like a solution with VBA, just say so - I would always assume that a non-VBA solution is preferred, if not obvious or stated otherwise. By the way, it would be nice, if you'd post your solution, might help someone else too ;) –  Jook Nov 12 '12 at 16:31
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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