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.

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

copyingthe list from one workbook to the other:Solution 1:

Instead of

copyingthe list from the other workbook,referenceit in a "helper column"e.g. Place this in C1:

=[Book2.xls]Sheet1!$A1

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

=NOT(ISNA(MATCH(A1,$C$1:$C$6,0)))

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:

=NOT(ISNA(MATCH(A1,[Book2.xls]Sheet1!$A$1:$A$6,0)))

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

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

=C1=TRUE

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

thanks DerbyDad03 it worked.

Ask Your Question

Weekly Poll