| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
If Conditional formatting
Hi Everyone,
Following is my code, which is used more than once in the Sub. Range("F88").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=E88<1" Selection.FormatConditions(1).Font.ColorIndex = 15 Selection.FormatConditions(1).Interior.ColorIndex = 15 Easy enough I suppose. On F88, there will be an error because the formula returns a #NUM if there's nothing to calucula, but when I print, even though the font is the same color as the cell, it will print out in black, can that be modified? Ideally, I'd like to make an if statement that if E88 is zero, simply shade the cell, otherwise perform the following formula. Range("F88").Select Selection.FormulaArray = _ "=MEDIAN(IF(R[-86]C[50]:R[1412]C[50]=""NOF"",R[-86]C[54]:R[1412]C[54]))" Thanks in advance for your help! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
If Conditional formatting
You may want to use one of the "IS" functionions in VBA
IsArray IsDate IsError IsMissing IsNull IsNummeric IsObject "CV323" wrote: Hi Everyone, Following is my code, which is used more than once in the Sub. Range("F88").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=E88<1" Selection.FormatConditions(1).Font.ColorIndex = 15 Selection.FormatConditions(1).Interior.ColorIndex = 15 Easy enough I suppose. On F88, there will be an error because the formula returns a #NUM if there's nothing to calucula, but when I print, even though the font is the same color as the cell, it will print out in black, can that be modified? Ideally, I'd like to make an if statement that if E88 is zero, simply shade the cell, otherwise perform the following formula. Range("F88").Select Selection.FormulaArray = _ "=MEDIAN(IF(R[-86]C[50]:R[1412]C[50]=""NOF"",R[-86]C[54]:R[1412]C[54]))" Thanks in advance for your help! |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
If Conditional formatting
Thanks Joel, but I'm not sure what you mean. Where do I put "IS" function?
"Joel" wrote: You may want to use one of the "IS" functionions in VBA IsArray IsDate IsError IsMissing IsNull IsNummeric IsObject "CV323" wrote: Hi Everyone, Following is my code, which is used more than once in the Sub. Range("F88").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=E88<1" Selection.FormatConditions(1).Font.ColorIndex = 15 Selection.FormatConditions(1).Interior.ColorIndex = 15 Easy enough I suppose. On F88, there will be an error because the formula returns a #NUM if there's nothing to calucula, but when I print, even though the font is the same color as the cell, it will print out in black, can that be modified? Ideally, I'd like to make an if statement that if E88 is zero, simply shade the cell, otherwise perform the following formula. Range("F88").Select Selection.FormulaArray = _ "=MEDIAN(IF(R[-86]C[50]:R[1412]C[50]=""NOF"",R[-86]C[54]:R[1412]C[54]))" Thanks in advance for your help! |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
If Conditional formatting
And then what?
Not sure what you mean "Joel" wrote: You may want to use one of the "IS" functionions in VBA IsArray IsDate IsError IsMissing IsNull IsNummeric IsObject "CV323" wrote: Hi Everyone, Following is my code, which is used more than once in the Sub. Range("F88").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=E88<1" Selection.FormatConditions(1).Font.ColorIndex = 15 Selection.FormatConditions(1).Interior.ColorIndex = 15 Easy enough I suppose. On F88, there will be an error because the formula returns a #NUM if there's nothing to calucula, but when I print, even though the font is the same color as the cell, it will print out in black, can that be modified? Ideally, I'd like to make an if statement that if E88 is zero, simply shade the cell, otherwise perform the following formula. Range("F88").Select Selection.FormulaArray = _ "=MEDIAN(IF(R[-86]C[50]:R[1412]C[50]=""NOF"",R[-86]C[54]:R[1412]C[54]))" Thanks in advance for your help! |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
If Conditional formatting
You are testing =E88<1, this means yu are expecting a number but not finding
a number in the cell. Not quite should what you want to do, but below is what I think you are asking for. Range("F88").Select Selection.FormatConditions.Delete Range("F88").Select if isnumeric(range("E88") Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=E88<1" else Selection.FormulaArray = _ "=MEDIAN(IF(R[-86]C[50]:R[1412]C[50]=""NOF"",R[-86]C[54]:R[1412]C[54]))" end if Selection.FormatConditions(1).Font.ColorIndex = 15 Selection.FormatConditions(1).Interior.ColorIndex = 15 "CV323" wrote: Thanks Joel, but I'm not sure what you mean. Where do I put "IS" function? "Joel" wrote: You may want to use one of the "IS" functionions in VBA IsArray IsDate IsError IsMissing IsNull IsNummeric IsObject "CV323" wrote: Hi Everyone, Following is my code, which is used more than once in the Sub. Range("F88").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=E88<1" Selection.FormatConditions(1).Font.ColorIndex = 15 Selection.FormatConditions(1).Interior.ColorIndex = 15 Easy enough I suppose. On F88, there will be an error because the formula returns a #NUM if there's nothing to calucula, but when I print, even though the font is the same color as the cell, it will print out in black, can that be modified? Ideally, I'd like to make an if statement that if E88 is zero, simply shade the cell, otherwise perform the following formula. Range("F88").Select Selection.FormulaArray = _ "=MEDIAN(IF(R[-86]C[50]:R[1412]C[50]=""NOF"",R[-86]C[54]:R[1412]C[54]))" Thanks in advance for your help! |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
If Conditional formatting
Thanks Joel,
I'll give it a try. Range E88 will contain a zero or above. Only when it's a zero do I want the F88 to be grayed out. I'll work on that project again in the morning, and may come back again. This is appreciated. "Joel" wrote: You are testing =E88<1, this means yu are expecting a number but not finding a number in the cell. Not quite should what you want to do, but below is what I think you are asking for. Range("F88").Select Selection.FormatConditions.Delete Range("F88").Select if isnumeric(range("E88") Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=E88<1" else Selection.FormulaArray = _ "=MEDIAN(IF(R[-86]C[50]:R[1412]C[50]=""NOF"",R[-86]C[54]:R[1412]C[54]))" end if Selection.FormatConditions(1).Font.ColorIndex = 15 Selection.FormatConditions(1).Interior.ColorIndex = 15 "CV323" wrote: Thanks Joel, but I'm not sure what you mean. Where do I put "IS" function? "Joel" wrote: You may want to use one of the "IS" functionions in VBA IsArray IsDate IsError IsMissing IsNull IsNummeric IsObject "CV323" wrote: Hi Everyone, Following is my code, which is used more than once in the Sub. Range("F88").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=E88<1" Selection.FormatConditions(1).Font.ColorIndex = 15 Selection.FormatConditions(1).Interior.ColorIndex = 15 Easy enough I suppose. On F88, there will be an error because the formula returns a #NUM if there's nothing to calucula, but when I print, even though the font is the same color as the cell, it will print out in black, can that be modified? Ideally, I'd like to make an if statement that if E88 is zero, simply shade the cell, otherwise perform the following formula. Range("F88").Select Selection.FormulaArray = _ "=MEDIAN(IF(R[-86]C[50]:R[1412]C[50]=""NOF"",R[-86]C[54]:R[1412]C[54]))" Thanks in advance for your help! |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
If Conditional formatting
A zero in the cell E88 would not give a #value error when testing with <1.
Problem is being caused for some other reason. I think becasue the cell doesn't contain a number (possibly empty). "CV323" wrote: Thanks Joel, I'll give it a try. Range E88 will contain a zero or above. Only when it's a zero do I want the F88 to be grayed out. I'll work on that project again in the morning, and may come back again. This is appreciated. "Joel" wrote: You are testing =E88<1, this means yu are expecting a number but not finding a number in the cell. Not quite should what you want to do, but below is what I think you are asking for. Range("F88").Select Selection.FormatConditions.Delete Range("F88").Select if isnumeric(range("E88") Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=E88<1" else Selection.FormulaArray = _ "=MEDIAN(IF(R[-86]C[50]:R[1412]C[50]=""NOF"",R[-86]C[54]:R[1412]C[54]))" end if Selection.FormatConditions(1).Font.ColorIndex = 15 Selection.FormatConditions(1).Interior.ColorIndex = 15 "CV323" wrote: Thanks Joel, but I'm not sure what you mean. Where do I put "IS" function? "Joel" wrote: You may want to use one of the "IS" functionions in VBA IsArray IsDate IsError IsMissing IsNull IsNummeric IsObject "CV323" wrote: Hi Everyone, Following is my code, which is used more than once in the Sub. Range("F88").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=E88<1" Selection.FormatConditions(1).Font.ColorIndex = 15 Selection.FormatConditions(1).Interior.ColorIndex = 15 Easy enough I suppose. On F88, there will be an error because the formula returns a #NUM if there's nothing to calucula, but when I print, even though the font is the same color as the cell, it will print out in black, can that be modified? Ideally, I'd like to make an if statement that if E88 is zero, simply shade the cell, otherwise perform the following formula. Range("F88").Select Selection.FormulaArray = _ "=MEDIAN(IF(R[-86]C[50]:R[1412]C[50]=""NOF"",R[-86]C[54]:R[1412]C[54]))" Thanks in advance for your help! |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
If Conditional formatting
Ok Joel,
C88, D88, E88, F88 is gathering data based on the criteria NOF from a list at the end of the report. C88 will sumproduct all male NOFs D88 will sumproduct all female NOFs E88 will sum both C88+D88. If there are no NOFs then E88 will be a zero. F88 will calculate the median age of all the NOFs, but if there are none, then F88 will return a #NUM. When I used this: Range("F88").Select Selection.FormulaArray = _ "=MEDIAN(IF(R[-86]C[50]:R[1412]C[50]=""NOF"",R[-86]C[54]:R[1412]C[54],0))" It returned zeros even if the criteria was true. So in order to bypass this error, I shaded the font as well, but when I print I can still the error. "Joel" wrote: A zero in the cell E88 would not give a #value error when testing with <1. Problem is being caused for some other reason. I think becasue the cell doesn't contain a number (possibly empty). "CV323" wrote: Thanks Joel, I'll give it a try. Range E88 will contain a zero or above. Only when it's a zero do I want the F88 to be grayed out. I'll work on that project again in the morning, and may come back again. This is appreciated. "Joel" wrote: You are testing =E88<1, this means yu are expecting a number but not finding a number in the cell. Not quite should what you want to do, but below is what I think you are asking for. Range("F88").Select Selection.FormatConditions.Delete Range("F88").Select if isnumeric(range("E88") Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=E88<1" else Selection.FormulaArray = _ "=MEDIAN(IF(R[-86]C[50]:R[1412]C[50]=""NOF"",R[-86]C[54]:R[1412]C[54]))" end if Selection.FormatConditions(1).Font.ColorIndex = 15 Selection.FormatConditions(1).Interior.ColorIndex = 15 "CV323" wrote: Thanks Joel, but I'm not sure what you mean. Where do I put "IS" function? "Joel" wrote: You may want to use one of the "IS" functionions in VBA IsArray IsDate IsError IsMissing IsNull IsNummeric IsObject "CV323" wrote: Hi Everyone, Following is my code, which is used more than once in the Sub. Range("F88").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=E88<1" Selection.FormatConditions(1).Font.ColorIndex = 15 Selection.FormatConditions(1).Interior.ColorIndex = 15 Easy enough I suppose. On F88, there will be an error because the formula returns a #NUM if there's nothing to calucula, but when I print, even though the font is the same color as the cell, it will print out in black, can that be modified? Ideally, I'd like to make an if statement that if E88 is zero, simply shade the cell, otherwise perform the following formula. Range("F88").Select Selection.FormulaArray = _ "=MEDIAN(IF(R[-86]C[50]:R[1412]C[50]=""NOF"",R[-86]C[54]:R[1412]C[54]))" Thanks in advance for your help! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formatting Conditional Formatting Icon Sets | Excel Discussion (Misc queries) | |||
| Formatting cells in a column with conditional formatting? | Excel Discussion (Misc queries) | |||
| Protect Cell Formatting including Conditional Formatting | Excel Discussion (Misc queries) | |||
| conditional Formatting based on cell formatting | Excel Worksheet Functions | |||
| Conditional Formatting that will display conditional data | Excel Worksheet Functions | |||
Linear Mode
