Solved conditional formatting depending upon another workbook

July 5, 2012 at 03:18:31
Specs: Windows Vista
I want to do conditional formatting in a range in a sheet say sheet1 in workbook1, depending upon a range in another workbook without coping that range into sheet1 of workbook1.
For example I have two workbook say 1 & 2. 1 contains a column with text values. & 2 also contains a list with text values. I would like to match value of cell in range of sheet1 of workbook1 with value in list of workbook2.
If it matches then its ok otherwise change the colour of cell .

Please help me out of this.
I'll be thankful to you.

See More: conditional formatting depending upon another workbook

Report •

July 5, 2012 at 06:28:23
✔ Best Answer
As far as I know, you can not use CF across workbooks. It can be done across worksheets using Named Ranges, but I don't think that works across workbooks.

2 possible solutions that don't involve actually copying the list from one workbook to the other:

Solution 1:

Instead of copying the list from the other workbook, reference it in a "helper column"

e.g. Place this in C1:


Then hide Column C and use this as your CF Rule:


Solution 2:

Use an formula to place a value in a helper column if a MATCH exists:

e.g. Place this in C1 and drag it down:


This will place TRUE in each Row where a MATCH exists.

Then hide Column C and use this as your CF Rule:


Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

July 11, 2012 at 04:37:02
thanks DerbyDad03 it worked.

Report •

Related Solutions

Ask Question