Conditional Formatting - Highlight All Rows in Table with Error in Any Cell

[jgray]

Hello -

I am working on a fairly large excel database - about 23,000 entries (rows). Multiple individuals are adding new entries to this table on a monthly basis, and we are seeing a number of errors in these entries, not always in the same column.

I am wanting to call attention to these entries that have errors within them. Is there any way to highlight all the rows in a table in which in there is an error?

I have attached a smaller version of the database I am working with.
PM2A Beneficiary Data _Excel Forum_3-18-2013.xlsx

[Jonmo1]

How are you defining "Error" ?
Do you mean formula errors like #N/A #VALUE! #DIV/0! etc?

You can use the ISERROR function in conditional formatting.

[FDibbins]

1. highlight the range you want to apply the conditional formatting to
2. on the home tab, styles, select CF
3. select new rule, select use formula
4. enter =ISERROR(A9) format fill as needed

I started my range in A9

[jgray]

Hi FDibbins -

Yes - I had tried this, but it only highlights the cells with the error occuring. I am hoping to highlight the entire row, if anywhere in that row, there is an error code.

Does this make sense?

[jgray]

Hi FDibbins -

Yes - I had tried this, but it only highlights the cells with the error occuring. I am hoping to highlight the entire row, if anywhere in that row, there is an error code.

Does this make sense?

[Jonmo1]

Hopefully you don't really want the ENTIRE Row...Perhaps just from column A to Z ..

Try
=ISERROR(SUM($A9:$Z9))

[FDibbins]

or, extend Jon's range to AF?

[Tony Valko]

Try this...

Select the *entire* range A9:AF340 starting from cell A9.
Cell A9 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:

=OR(ISERROR(A9:AF9))

Click the Format button
Select the desired style(s)
OK out

[FDibbins]

@ jgray, I just sawe that this is actually a duplicate thread. You have been a member long enough to know that is not allowed here - had I seen that before, I would have closed this thread.

I will close your other thread, in future, please do not post duplicate threads

[jgray]

Thanks for your reply FDibbins. I have had issues with the site today - many times receiving a message about the "server being busy" after trying to submit a posting. I re-submitted the post after having received this message thinking the original post had not actually been submitted.

[jgray]

Thanks Tony. Works perfectly.

[Tony Valko]

You're welcome. Thanks for the feedback!