Conditional formatting

[PIDIT]

Dear All,

I am using Excel 2007.

I have 2 sheets. Now I want to highlight only those cells in sheet 1, if corresponding cells in sheet 2 have text "error".

Please help. Thank you

 

[Steve=True]

I don't think you can apply conditional formatting across 2 sheets. Must be within the same sheet.

To do this, you might have to create a helper column in the sheet you want the conditional formatting to look at the other sheet.

Steve=True
www.exceldashboardtemplates.com

 

[PIDIT]

Dear Steve=True,

Thank you for your reply. I have not been able to fix it even if in same sheet. Can you help me?

 

[pedro-egoli]

This works in 2003.

Highlight cells that may contain "error".
Set condition as

 Cell Value is equal to error

Format as needed.

Pedro

 

[T. Valko]

Dear All,

I am using Excel 2007.

I have 2 sheets. Now I want to highlight only those cells in sheet 1, if corresponding cells in sheet 2 have text "error".

Please help. Thank you

Try this...

Let's assume the range to be formatted is Sheet1 A1:C5.

Select the *entire* range A1:C5 starting from cell A1.
Cell A1 will be the active cell. The active cell is the
one cell in the selected range that is not shaded. The
formula will be relative to the active cell.

Goto the Home tab>Styles>Conditional Formatting>Manage rules>New rule
Use a formula to determine which cells to format
Enter this formula in the box below:
=INDIRECT("'Sheet2'!RC",0)="error"
Click the Format button
Select the desired style(s)
OK out

 

[T. Valko]

I don't think you can apply conditional formatting across 2 sheets. Must be within the same sheet.

You can, you just can't directly reference the other sheet.

You can use named ranges to refer to the other sheet or you can use INDIRECT like I did in my other reply.

I think that I read somewhere that in Excel 2010 you CAN directly refer to other sheets to apply CF. Would someone either confirm or correct this, please?