I have a large sheet with numbers in rows starting in many different columns.
I want to find cells where the first number in a row is zero. In other words,
find occurances of (blank, zero) in adjacent cells. I have Office 2000.

Steve in Need

Assuming your data at A1 looks like this:
0 0 1 2 3 4
0 0 1 2 3
0 0 1 2
1 0 1
then this Conditional Format will highlight
only the first zero in the third row:
Enter this Conditional Format at B1 and
Copy Paste Special Format to all:
=ISNUMBER(B1)*(B1=0)*(SUM($A1:A1)=0)*(COUNTA($A1:A 1)=0)

"Herbert Seidenberg" wrote:

Assuming your data at A1 looks like this:
0 0 1 2 3 4
0 0 1 2 3
0 0 1 2
1 0 1
then this Conditional Format will highlight
only the first zero in the third row:
Enter this Conditional Format at B1 and
Copy Paste Special Format to all:
=ISNUMBER(B1)*(B1=0)*(SUM($A1:A1)=0)*(COUNTA($A1:A 1)=0)


I can't get this to work. Because of other material in the beginning columns
and the first row I use
=ISNUMBER(P2)*(P2=0)*(SUM($O2:O2)=0)*(COUNTA($O2:O 2)=0)
in N2. If entered into P2 there is a circular reference error.
Do I select the area I want to analyze and then Paste Special Formats,
which doesn't work.

Enter the CF formula this way (in my example):
Select B1
Format Conditional Formatting
from the dropdown list, select Formula Is
then type in the formula in the box next to it.
Select the format, say Format Pattern Red
Click OK
Select B1 Copy
Select A1:F4 Paste Special Formats
If you enter the formula into the formula bar of a cell,
you will destroy your original data
and get a circular reference.

"Herbert Seidenberg" wrote:

Enter the CF formula this way (in my example):
Select B1
Format Conditional Formatting
from the dropdown list, select Formula Is
then type in the formula in the box next to it.
Select the format, say Format Pattern Red
Click OK
Select B1 Copy
Select A1:F4 Paste Special Formats
If you enter the formula into the formula bar of a cell,
you will destroy your original data
and get a circular reference.


Thanks, works like a charm.