Can you copy conditional formatting to a different worksheet?

[KRayKeeling]

Hello all!!

I just put in a condition on Excel 2007 and I was wondering if I could just copy it onto another worksheet or if I have to type it all over again?

It would be awesome if I could link the condition to another worksheet... if anybody knows, please let me know!!

Thank you!

Kevin K.

[Richard Buttrey]

Hello all!!

I just put in a condition on Excel 2007 and I was wondering if I could just copy it onto another worksheet or if I have to type it all over again?

It would be awesome if I could link the condition to another worksheet... if anybody knows, please let me know!!

Thank you!

Kevin K.

Don't know whether things have changed with XL2007 but in older versions you can't use conditional formats which refer to other worksheets or workbooks.

However you could write your own pseudo conditional format using some VBA code.

Rgds

[Portuga]

Hi,

You cant use conditional formats which refer to other worksheets or workbooks, but you can copy them from one to another.

You can choose "paste formats" from the edit menu or just use the format painter icon.

[mikerickson]

You can Conditionaly format a cell based on data from other sheets using Named Ranges

If name: MyCell RefersTo: Sheet2!$A$1

and the Conditional formatting for Sheet1!B2 is =(myCell=3) then Sheet1 B2 will be highlited whenever Sheet2!$A$1 is 3.

You can also use relative/absolute refernecing in the name definition.

[Richard Buttrey]

You can Conditionaly format a cell based on data from other sheets using Named Ranges

If name: MyCell RefersTo: Sheet2!$A$1

and the Conditional formatting for Sheet1!B2 is =(myCell=3) then Sheet1 B2 will be highlited whenever Sheet2!$A$1 is 3.

You can also use relative/absolute refernecing in the name definition.

Thanks for that - you learn something every day.

Does this apply across workbooks or just worksheets?

Rgds

[mikerickson]

The problem across workbooks is the scope of the name. Scope is not what the name refers to, but which sheets/workbooks recognize the name as a referenced name.

If a name (myRange) is scoped at the worksheet level, then the formula =INDEX(myRange,1,1) will return that value when placed in a cell on that sheet. On a different sheet, that formula will return a #NAME error.

Names can be scoped to a worksheet, a workbook (the default) or to the whole application.

I haven't tested the across workbooks Condidtional Formatting, but if it does work, the name would have to be scoped at the Application level.

I know 2007 has better control of scoping names than my 2004, but the only way I know of scoping a name at the Application level requires VB.

Edit: On further thought, using a Name to refer to an INDIRECT might be a work-around .. must test.

[mikerickson]

Yes, that works

In Workbook1.xls, I defined
Name: otherBook RefersTo: =INDIRECT("[Workbook2.xls]Sheet1!$A$1")
(note that otherBook is scoped as a Workbook1 name, formulas in Workbook2 cannot reference otherBook.)

and then conditionaly formatted a cell with =(otherBook=1).

The conditional formatting worked. With some odd behaviour.

Closing Workbook2, leaving Workbook1 open, and the cell formatting did not change.

However, closing the application and opening Workbook1, but not Workbook2, the name refreshed to an error, and the cell showed the un-conditioned formatting.