Excel 2007 : condiitonal format sheet 1 from values in sheet2

**shramh** · 10-21-2011, 09:29 AM

I am using MS Excel 2007. I want to conditionally format sheet 1 based on the values in sheet 2. I have 10000 x 1000 rows in sheet 1 and sheet 2.

I tried to conditonally format a single row with following formula:

Please Login or Register  to view this content.

Edits: Small sample example is attached, where I did conditional formatting in column 1.

I defined sheet2 colA as Sheet2_ColA used in naming column function. I tried to name whole sheet 2 and apply to sheet 1 but do not work.

As I have huge number of column to deal with I can not do column by column (at least will not be justified in computer age !

)

thank you

**NBVC** · 10-21-2011, 09:31 AM

What exactly are you trying to do? I.e. what is the logic you are looking for in layman's terms?

**shramh** · 10-21-2011, 10:01 AM

I want apply conditional format to sheet 1 ( whole sheet, i.e. comulns and rows) based on sheet 2 ( whole sheet 2, i.e. coulmn and rows).

Means that if value in sheet2 A1 cell is > 0.5, the sheet1 A1 cell will be formatted as "red" color.

Similarly for all corresponding cells in sheet 1 are conditionally formated based on sheet 2. I could do one by one for each column wise (see edits with example for column 1 attached "example1.xls" ).

I have more than 10000 columns so I want to repeate the same process to whole worksheet.

thanks;

**NBVC** · 10-21-2011, 10:13 AM

You can apply a name to the whole of sheet2 (although it is recommended you limit the columns and rows because conditional formatting is volatile and therefore recalculates for all cells when you make a change in just one).

so if you select and name Sheet2!A1:L18 like Sheet2Data

Then in sheet1 select A1:L18 and apply conditional formula:

=AND(A1<>"",INDEX(Sheet2Data,ROW(),COLUMN())<0.5)