Conditional Formating in Blank Cells
I have a set of data which I have highlighted values 3 in red and values
<0.3 in green. Using these criteria all of my blank cells end up with green highlighting, though I would rather have no color. I've set a condition for no color when cells are blank and also tried to set a condition for no color when the cell value = "a blank cell". No matter what I do, the cell remains highlighted. I can't simply go to blank cells and clear the rules, because I need to sort the cells later and the formating doesn't migrate with the value when it moves to another location. Any ideas on how to clear the highlighting from the blank cells? |
Conditional Formating in Blank Cells
Hi,
in conditional formating enter =AND(A10,A1<0.3) if this helps please click yes, thanks "TF" wrote: I have a set of data which I have highlighted values 3 in red and values <0.3 in green. Using these criteria all of my blank cells end up with green highlighting, though I would rather have no color. I've set a condition for no color when cells are blank and also tried to set a condition for no color when the cell value = "a blank cell". No matter what I do, the cell remains highlighted. I can't simply go to blank cells and clear the rules, because I need to sort the cells later and the formating doesn't migrate with the value when it moves to another location. Any ideas on how to clear the highlighting from the blank cells? |
Conditional Formating in Blank Cells
Since the OP didn't say they *didn't* have any negative values it'd be safer
to use: =AND(COUNT(A1),A1<0.3) -- Biff Microsoft Excel MVP "Eduardo" wrote in message ... Hi, in conditional formating enter =AND(A10,A1<0.3) if this helps please click yes, thanks "TF" wrote: I have a set of data which I have highlighted values 3 in red and values <0.3 in green. Using these criteria all of my blank cells end up with green highlighting, though I would rather have no color. I've set a condition for no color when cells are blank and also tried to set a condition for no color when the cell value = "a blank cell". No matter what I do, the cell remains highlighted. I can't simply go to blank cells and clear the rules, because I need to sort the cells later and the formating doesn't migrate with the value when it moves to another location. Any ideas on how to clear the highlighting from the blank cells? |
Conditional Formating in Blank Cells
These seem to work for a single cell, but I don't understand how to formulate
the formula for a block of cells (I've tried replacing A1 in these formulas with $b$2:$f$62). "T. Valko" wrote: Since the OP didn't say they *didn't* have any negative values it'd be safer to use: =AND(COUNT(A1),A1<0.3) -- Biff Microsoft Excel MVP "Eduardo" wrote in message ... Hi, in conditional formating enter =AND(A10,A1<0.3) if this helps please click yes, thanks "TF" wrote: I have a set of data which I have highlighted values 3 in red and values <0.3 in green. Using these criteria all of my blank cells end up with green highlighting, though I would rather have no color. I've set a condition for no color when cells are blank and also tried to set a condition for no color when the cell value = "a blank cell". No matter what I do, the cell remains highlighted. I can't simply go to blank cells and clear the rules, because I need to sort the cells later and the formating doesn't migrate with the value when it moves to another location. Any ideas on how to clear the highlighting from the blank cells? |
Conditional Formating in Blank Cells
If you want to apply the formatting to a range of cells...
Assuming the range of interest is B2:F62. Select the entire range of cells starting from cell B2. Cell B2 will be the active cell. The active cell is the single cell in the selected range that *is not* shaded. The formula is relative and based on the active cell. The cell references will automatically adjust to the other cells in the applied range. Select the range B2:B62 Goto the menu FormatConditional Formatting Condition 1 Select the Formula Is option Enter this formula in the box on the right: =AND(COUNT(B2),B2<0.3) Click the Format button Select the desired style(s) OK Click the Add button Condition 2 Select the Formula Is option Enter this formula in the box on the right: =AND(COUNT(B2),B23) Click the Format button Select the desired style(s) OK out -- Biff Microsoft Excel MVP "TF" wrote in message ... These seem to work for a single cell, but I don't understand how to formulate the formula for a block of cells (I've tried replacing A1 in these formulas with $b$2:$f$62). "T. Valko" wrote: Since the OP didn't say they *didn't* have any negative values it'd be safer to use: =AND(COUNT(A1),A1<0.3) -- Biff Microsoft Excel MVP "Eduardo" wrote in message ... Hi, in conditional formating enter =AND(A10,A1<0.3) if this helps please click yes, thanks "TF" wrote: I have a set of data which I have highlighted values 3 in red and values <0.3 in green. Using these criteria all of my blank cells end up with green highlighting, though I would rather have no color. I've set a condition for no color when cells are blank and also tried to set a condition for no color when the cell value = "a blank cell". No matter what I do, the cell remains highlighted. I can't simply go to blank cells and clear the rules, because I need to sort the cells later and the formating doesn't migrate with the value when it moves to another location. Any ideas on how to clear the highlighting from the blank cells? |
All times are GMT +1. The time now is 06:16 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com