Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting on empty cells
I'm trying to apply conditional formatting on column g where the formula in
each cell is =IF(c5,g5/c5,"") or as appropriate for that row and it returns a percent value. I want those values greater than $G$4 to be highlighted in red. The value in G4 is 10%. Two problems. 1) My highlighting returns all my blank cells in column G in red 2) If I try to format the 'cell value $G$4" and the value is greater than 10%, the highlighting doesn't show. If I use the formatting of 'cell value ..1' then it works. I want to be able to change the value in G4 so I need it to work the first way. I hope this makes sense. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting on empty cells
The problem is due to your formulas returning a formula blank ("") which is
a TEXT value. In Excel, a text value will *always* evaluate to be greater than any number. "text" 1,000,000,000,000 = TRUE So: "" $G$4 = TRUE and the formatting is being applied. Use the Formula Is option and test that the cell(s) do in fact contain a number: =AND(COUNT(A1),A1$G$4) -- Biff Microsoft Excel MVP "mgccoop" wrote in message ... I'm trying to apply conditional formatting on column g where the formula in each cell is =IF(c5,g5/c5,"") or as appropriate for that row and it returns a percent value. I want those values greater than $G$4 to be highlighted in red. The value in G4 is 10%. Two problems. 1) My highlighting returns all my blank cells in column G in red 2) If I try to format the 'cell value $G$4" and the value is greater than 10%, the highlighting doesn't show. If I use the formatting of 'cell value .1' then it works. I want to be able to change the value in G4 so I need it to work the first way. I hope this makes sense. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting on empty cells
OK I get the info about text always being greater than any value now.
I'm sorry I don't understand the reference to the Count A1. How does that impact my text value? If I want to apply your formula below to all the cells in column g (g8:g38) how do I do that? Can I highlight the range and apply once formula or do I have to do it for each cell in the range? Thanks for your help "T. Valko" wrote: The problem is due to your formulas returning a formula blank ("") which is a TEXT value. In Excel, a text value will *always* evaluate to be greater than any number. "text" 1,000,000,000,000 = TRUE So: "" $G$4 = TRUE and the formatting is being applied. Use the Formula Is option and test that the cell(s) do in fact contain a number: =AND(COUNT(A1),A1$G$4) -- Biff Microsoft Excel MVP "mgccoop" wrote in message ... I'm trying to apply conditional formatting on column g where the formula in each cell is =IF(c5,g5/c5,"") or as appropriate for that row and it returns a percent value. I want those values greater than $G$4 to be highlighted in red. The value in G4 is 10%. Two problems. 1) My highlighting returns all my blank cells in column G in red 2) If I try to format the 'cell value $G$4" and the value is greater than 10%, the highlighting doesn't show. If I use the formatting of 'cell value .1' then it works. I want to be able to change the value in G4 so I need it to work the first way. I hope this makes sense. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting on empty cells
I used A1 as generic reference.
If the cell(s) that contain your formulas return the formula blank COUNT tests those cells to make sure there is a number returned by that formula. If the formula returns the formula blank COUNT = 0 and this causes the AND function to be FALSE and not apply the format. If the formula returns a number then COUNT = 1. If the number is G4 then the format is applied. I want to apply your formula below to all the cells in column g (g8:g38) You can set the formatting all at one time: Select the range G8:G38 so that G8 is the active cell. The active cell is the one that's not highlighted. Goto the menu FormatConditional Formatting Select the Formula Is option Enter this formula in the little box to the right: =AND(COUNT(G8),G8$G$4) Enter the formula *exactly* as above. Don't change it! Click the Format button Select the desired style(s) OK out -- Biff Microsoft Excel MVP "mgccoop" wrote in message ... OK I get the info about text always being greater than any value now. I'm sorry I don't understand the reference to the Count A1. How does that impact my text value? If I want to apply your formula below to all the cells in column g (g8:g38) how do I do that? Can I highlight the range and apply once formula or do I have to do it for each cell in the range? Thanks for your help "T. Valko" wrote: The problem is due to your formulas returning a formula blank ("") which is a TEXT value. In Excel, a text value will *always* evaluate to be greater than any number. "text" 1,000,000,000,000 = TRUE So: "" $G$4 = TRUE and the formatting is being applied. Use the Formula Is option and test that the cell(s) do in fact contain a number: =AND(COUNT(A1),A1$G$4) -- Biff Microsoft Excel MVP "mgccoop" wrote in message ... I'm trying to apply conditional formatting on column g where the formula in each cell is =IF(c5,g5/c5,"") or as appropriate for that row and it returns a percent value. I want those values greater than $G$4 to be highlighted in red. The value in G4 is 10%. Two problems. 1) My highlighting returns all my blank cells in column G in red 2) If I try to format the 'cell value $G$4" and the value is greater than 10%, the highlighting doesn't show. If I use the formatting of 'cell value .1' then it works. I want to be able to change the value in G4 so I need it to work the first way. I hope this makes sense. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting on empty cells
P.S.
If you want to make this really robust you might also want to test and make sure G4 does in fact contain a number. As it is, if G4 is empty and Gn contains a number the formatting will be applied. So: =AND(COUNT($G$4,G8)=2,G8$G$4) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... I used A1 as generic reference. If the cell(s) that contain your formulas return the formula blank COUNT tests those cells to make sure there is a number returned by that formula. If the formula returns the formula blank COUNT = 0 and this causes the AND function to be FALSE and not apply the format. If the formula returns a number then COUNT = 1. If the number is G4 then the format is applied. I want to apply your formula below to all the cells in column g (g8:g38) You can set the formatting all at one time: Select the range G8:G38 so that G8 is the active cell. The active cell is the one that's not highlighted. Goto the menu FormatConditional Formatting Select the Formula Is option Enter this formula in the little box to the right: =AND(COUNT(G8),G8$G$4) Enter the formula *exactly* as above. Don't change it! Click the Format button Select the desired style(s) OK out -- Biff Microsoft Excel MVP "mgccoop" wrote in message ... OK I get the info about text always being greater than any value now. I'm sorry I don't understand the reference to the Count A1. How does that impact my text value? If I want to apply your formula below to all the cells in column g (g8:g38) how do I do that? Can I highlight the range and apply once formula or do I have to do it for each cell in the range? Thanks for your help "T. Valko" wrote: The problem is due to your formulas returning a formula blank ("") which is a TEXT value. In Excel, a text value will *always* evaluate to be greater than any number. "text" 1,000,000,000,000 = TRUE So: "" $G$4 = TRUE and the formatting is being applied. Use the Formula Is option and test that the cell(s) do in fact contain a number: =AND(COUNT(A1),A1$G$4) -- Biff Microsoft Excel MVP "mgccoop" wrote in message ... I'm trying to apply conditional formatting on column g where the formula in each cell is =IF(c5,g5/c5,"") or as appropriate for that row and it returns a percent value. I want those values greater than $G$4 to be highlighted in red. The value in G4 is 10%. Two problems. 1) My highlighting returns all my blank cells in column G in red 2) If I try to format the 'cell value $G$4" and the value is greater than 10%, the highlighting doesn't show. If I use the formatting of 'cell value .1' then it works. I want to be able to change the value in G4 so I need it to work the first way. I hope this makes sense. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting on empty cells
Thank you - that is working for me now!
"T. Valko" wrote: I used A1 as generic reference. If the cell(s) that contain your formulas return the formula blank COUNT tests those cells to make sure there is a number returned by that formula. If the formula returns the formula blank COUNT = 0 and this causes the AND function to be FALSE and not apply the format. If the formula returns a number then COUNT = 1. If the number is G4 then the format is applied. I want to apply your formula below to all the cells in column g (g8:g38) You can set the formatting all at one time: Select the range G8:G38 so that G8 is the active cell. The active cell is the one that's not highlighted. Goto the menu FormatConditional Formatting Select the Formula Is option Enter this formula in the little box to the right: =AND(COUNT(G8),G8$G$4) Enter the formula *exactly* as above. Don't change it! Click the Format button Select the desired style(s) OK out -- Biff Microsoft Excel MVP "mgccoop" wrote in message ... OK I get the info about text always being greater than any value now. I'm sorry I don't understand the reference to the Count A1. How does that impact my text value? If I want to apply your formula below to all the cells in column g (g8:g38) how do I do that? Can I highlight the range and apply once formula or do I have to do it for each cell in the range? Thanks for your help "T. Valko" wrote: The problem is due to your formulas returning a formula blank ("") which is a TEXT value. In Excel, a text value will *always* evaluate to be greater than any number. "text" 1,000,000,000,000 = TRUE So: "" $G$4 = TRUE and the formatting is being applied. Use the Formula Is option and test that the cell(s) do in fact contain a number: =AND(COUNT(A1),A1$G$4) -- Biff Microsoft Excel MVP "mgccoop" wrote in message ... I'm trying to apply conditional formatting on column g where the formula in each cell is =IF(c5,g5/c5,"") or as appropriate for that row and it returns a percent value. I want those values greater than $G$4 to be highlighted in red. The value in G4 is 10%. Two problems. 1) My highlighting returns all my blank cells in column G in red 2) If I try to format the 'cell value $G$4" and the value is greater than 10%, the highlighting doesn't show. If I use the formatting of 'cell value .1' then it works. I want to be able to change the value in G4 so I need it to work the first way. I hope this makes sense. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting on empty cells
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "mgccoop" wrote in message ... Thank you - that is working for me now! "T. Valko" wrote: I used A1 as generic reference. If the cell(s) that contain your formulas return the formula blank COUNT tests those cells to make sure there is a number returned by that formula. If the formula returns the formula blank COUNT = 0 and this causes the AND function to be FALSE and not apply the format. If the formula returns a number then COUNT = 1. If the number is G4 then the format is applied. I want to apply your formula below to all the cells in column g (g8:g38) You can set the formatting all at one time: Select the range G8:G38 so that G8 is the active cell. The active cell is the one that's not highlighted. Goto the menu FormatConditional Formatting Select the Formula Is option Enter this formula in the little box to the right: =AND(COUNT(G8),G8$G$4) Enter the formula *exactly* as above. Don't change it! Click the Format button Select the desired style(s) OK out -- Biff Microsoft Excel MVP "mgccoop" wrote in message ... OK I get the info about text always being greater than any value now. I'm sorry I don't understand the reference to the Count A1. How does that impact my text value? If I want to apply your formula below to all the cells in column g (g8:g38) how do I do that? Can I highlight the range and apply once formula or do I have to do it for each cell in the range? Thanks for your help "T. Valko" wrote: The problem is due to your formulas returning a formula blank ("") which is a TEXT value. In Excel, a text value will *always* evaluate to be greater than any number. "text" 1,000,000,000,000 = TRUE So: "" $G$4 = TRUE and the formatting is being applied. Use the Formula Is option and test that the cell(s) do in fact contain a number: =AND(COUNT(A1),A1$G$4) -- Biff Microsoft Excel MVP "mgccoop" wrote in message ... I'm trying to apply conditional formatting on column g where the formula in each cell is =IF(c5,g5/c5,"") or as appropriate for that row and it returns a percent value. I want those values greater than $G$4 to be highlighted in red. The value in G4 is 10%. Two problems. 1) My highlighting returns all my blank cells in column G in red 2) If I try to format the 'cell value $G$4" and the value is greater than 10%, the highlighting doesn't show. If I use the formatting of 'cell value .1' then it works. I want to be able to change the value in G4 so I need it to work the first way. I hope this makes sense. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting on empty cells | Excel Discussion (Misc queries) | |||
Formatting cells in a column with conditional formatting? | Excel Discussion (Misc queries) | |||
How can I deactivate conditional formatting if the cell is empty? | Excel Discussion (Misc queries) | |||
Excel - Autom. Filter "Empty / Non Empty cells" should come first | Excel Discussion (Misc queries) | |||
Conditional Formatting Multiple cells based on 2 cells | Excel Worksheet Functions |