Highlight rows based on '1' or blank in all columns on a row

[Scott Lindstrom]

I have been playing with Excel 2007 conditional formatting here for a
while with no progress, so I thought I'd turn here for help.

I have a spreadsheet of patch numbers and the systems to which they've
been applied. If the patch is applied a '1' is in the cell; if not
the cell is blank:

PHSS_35884 1 1 1 1 1 1 1
PHKL_33110 1 1 1 1
PHKL_38311 1 1 1
PHKL_34909 1 1 1 1 1 1 1
PHNE_32057 1 1 1 1 1 1 1
PHKL_37263 1 1 1

What I'd like to do is have the row highlighted (or color set) if not
all the cells in the row are one (meaning the patch is not yet applied
to all systems). Is this easily done?

TIA,
Scott

 

[AG]

Hi Scott,

Try this -

1. Select the first row of cells and then click on Home > Conditional
Formatting > New Rule (I selected cells A1:H1)
2. Use the Rule Type --> Use a formula to determine which cells to
format.
3. Type the formula in their as =SUM($B1:$H1)<7 (Note: 7 is the total
number of systems where the patch must be applied, that is number of
columns. If you have more columns then change this number accordingly.
4. Define the formatting as you need that is the fill or fonts that
you need when above condition is true. Click on OK.
5. Now Copy the cells from A1:H1.
6.Select all the rows where you want the formatting to be done the
same way and do Paste Special > Paste Special > Formats.

You should be able to see all the rows where all columns do not
contain 1 highlighted in your selected format.

-AG

 

[Shane Devenshire]

Hi,

Save a few steps and highlight all the cells you want formatted, say
B2:L100, where titles are on row 1 and PH numbers in column A.

The proceed as suggested by John.

This will eliminate the need for steps 5 and 6.

 

[Scott Lindstrom]

Hi Scott,

Try this -

1. Select the first row of cells and then click on Home > Conditional
Formatting > New Rule (I selected cells A1:H1)
2. Use the Rule Type --> Use a formula to determine which cells to
format.
3. Type the formula in their as =SUM($B1:$H1)<7 (Note: 7 is the total
number of systems where the patch must be applied, that is number of
columns. If you have more columns then change this number accordingly.
4. Define the formatting as you need that is the fill or fonts that
you need when above condition is true. Click on OK.
5. Now Copy the cells from A1:H1.
6.Select all the rows where you want the formatting to be done the
same way and do Paste Special > Paste Special > Formats.

You should be able to see all the rows where all columns do not
contain 1 highlighted in your selected format.

-AG

AG - that worked great. Thanks!