|
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! |
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! |
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! |
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! |
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! |
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! |
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! |
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! |
| All times are GMT +1. The time now is 01:23 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com