Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Highlight rows based on '1' or blank in all columns on a row
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Highlight rows based on '1' or blank in all columns on a row
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Highlight rows based on '1' or blank in all columns on a row
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. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Scott Lindstrom" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Highlight rows based on '1' or blank in all columns on a row
On Jun 3, 5:14*pm, AG wrote:
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
conditional formatting:highlight row based on blank or non-blank c | Excel Worksheet Functions | |||
Highlighting rows/columns is there a way to darken the highlight? | Excel Discussion (Misc queries) | |||
How to Highlight Two Rows With Two Columns of Duplicate Values | Excel Worksheet Functions | |||
conditional formatting:highlight row based on blank or non-blank c | Excel Discussion (Misc queries) | |||
Isolate rows based on highlight (Excel 2003) | Excel Worksheet Functions |