If Conditional formatting

G

Guest

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!
 
G

Guest

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

Guest

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

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


CV323 said:
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!
Click to expand...
Click to expand...
 
G

Guest

And then what?

Not sure what you mean

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


CV323 said:
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!
Click to expand...
Click to expand...
 
G

Guest

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 said:
Thanks Joel, but I'm not sure what you mean. Where do I put "IS" function?

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


CV323 said:
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!
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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 said:
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 said:
Thanks Joel, but I'm not sure what you mean. Where do I put "IS" function?

Joel said:
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!
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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 said:
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 said:
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 said:
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!
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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 said:
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 said:
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 said:
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!
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

conditional formatting to print 1
Error when no records meet criteria 2
Error when no records meet criteria - repost 3
macro problem 8
4 conditional formats 2
More than 4 conditional formating? 14
alternate rowshading using conditional format excel 2007/2010 6
Conditional Formatting Code 4

Top