If Conditional formatting

[CV323]

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!

 

[Joel]

You may want to use one of the "IS" functionions in VBA
IsArray
IsDate
IsError
IsMissing
IsNull
IsNummeric
IsObject

 

[CV323]

Thanks Joel, but I'm not sure what you mean. Where do I put "IS" function?

You may want to use one of the "IS" functionions in VBA
IsArray
IsDate
IsError
IsMissing
IsNull
IsNummeric
IsObject

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!

 

[CV323]

And then what?

Not sure what you mean

You may want to use one of the "IS" functionions in VBA
IsArray
IsDate
IsError
IsMissing
IsNull
IsNummeric
IsObject

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!

 

[Joel]

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

Thanks Joel, but I'm not sure what you mean. Where do I put "IS" function?

You may want to use one of the "IS" functionions in VBA
IsArray
IsDate
IsError
IsMissing
IsNull
IsNummeric
IsObject

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!

 

[CV323]

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.

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

Thanks Joel, but I'm not sure what you mean. Where do I put "IS" function?

You may want to use one of the "IS" functionions in VBA
IsArray
IsDate
IsError
IsMissing
IsNull
IsNummeric
IsObject


:

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!

 

[Joel]

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).

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.

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

Thanks Joel, but I'm not sure what you mean. Where do I put "IS" function?

:

You may want to use one of the "IS" functionions in VBA
IsArray
IsDate
IsError
IsMissing
IsNull
IsNummeric
IsObject


:

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!

 

[CV323]

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.

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).

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.

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




:

Thanks Joel, but I'm not sure what you mean. Where do I put "IS" function?

:

You may want to use one of the "IS" functionions in VBA
IsArray
IsDate
IsError
IsMissing
IsNull
IsNummeric
IsObject


:

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!