How to Find a Specific First Number in a Row

[Guest]

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

 

[Herbert Seidenberg]

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:A1)=0)

 

[Guest]

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:A1)=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:O2)=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.

 

[Herbert Seidenberg]

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.

 

[Guest]

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.