Conditional Formatting Issue
 

I use the following formulas which are conditionally formatted in column J:

=IF(D1679="","",D1679/E1679)
=IF(D1680="","",D1680/E1680)
=IF(D1681="","",D1681/E1681)
=IF(D1682="","",D1682/E1682)
=IF(D1683="","",D1683/E1683)
=IF(D1684="","",D1684/E1684)
€¦

If the cell D1681 is blank, for example, the formatting on the blank cell
fills with the color green in J1681 blank cell, and then through the end of
the worksheet row J2500.

These blank green cells are distracting, if the cell is blank I would prefer
the green fill from the conditional formatting does not appear, I would like
a blank cell. I would like the conditional formatting to apply to cells that
contain values only.

Do you know of a way to mask this green formatting from appearing if the
cell value is blank?

I am currently using Excel 2007, the original worksheet was Excel 97-2003
which I €œsaved as€� in the Excel Worksheet format.

I have literally searched for hours trying to resolve this issue, and the
€œstop if true€� flag does not work. I have also read a significant amount of
help on conditional formatting to no avail.

Any help will be greatly appreciated.

Conditional Formatting Issue
 

What is your actual Conditional Formatting formula(s)?

--
Rick (MVP - Excel)


"Robert" wrote in message
...
I use the following formulas which are conditionally formatted in column J:

=IF(D1679="","",D1679/E1679)
=IF(D1680="","",D1680/E1680)
=IF(D1681="","",D1681/E1681)
=IF(D1682="","",D1682/E1682)
=IF(D1683="","",D1683/E1683)
=IF(D1684="","",D1684/E1684)
€¦

If the cell D1681 is blank, for example, the formatting on the blank cell
fills with the color green in J1681 blank cell, and then through the end
of
the worksheet row J2500.

These blank green cells are distracting, if the cell is blank I would
prefer
the green fill from the conditional formatting does not appear, I would
like
a blank cell. I would like the conditional formatting to apply to cells
that
contain values only.

Do you know of a way to mask this green formatting from appearing if the
cell value is blank?

I am currently using Excel 2007, the original worksheet was Excel 97-2003
which I €œsaved as€� in the Excel Worksheet format.

I have literally searched for hours trying to resolve this issue, and the
€œstop if true€� flag does not work. I have also read a significant amount
of
help on conditional formatting to no avail.

Any help will be greatly appreciated.

Conditional Formatting Issue
 

Cell Value = 3

"Rick Rothstein" wrote:

What is your actual Conditional Formatting formula(s)?

--
Rick (MVP - Excel)


"Robert" wrote in message
...
I use the following formulas which are conditionally formatted in column J:

=IF(D1679="","",D1679/E1679)
=IF(D1680="","",D1680/E1680)
=IF(D1681="","",D1681/E1681)
=IF(D1682="","",D1682/E1682)
=IF(D1683="","",D1683/E1683)
=IF(D1684="","",D1684/E1684)
€¦

If the cell D1681 is blank, for example, the formatting on the blank cell
fills with the color green in J1681 blank cell, and then through the end
of
the worksheet row J2500.

These blank green cells are distracting, if the cell is blank I would
prefer
the green fill from the conditional formatting does not appear, I would
like
a blank cell. I would like the conditional formatting to apply to cells
that
contain values only.

Do you know of a way to mask this green formatting from appearing if the
cell value is blank?

I am currently using Excel 2007, the original worksheet was Excel 97-2003
which I €œsaved as€� in the Excel Worksheet format.

I have literally searched for hours trying to resolve this issue, and the
€œstop if true€� flag does not work. I have also read a significant amount
of
help on conditional formatting to no avail.

Any help will be greatly appreciated.

Conditional Formatting Issue
 

That doesn't shade a truly blank cell, but perhaps a text string (which
might even be a empty string "")?

Try changing Cell Value Is to Formula Is, and use =AND(ISNUMBER(B1),B1=3)
--
David Biddulph

"Robert" wrote in message
...
Cell Value = 3

"Rick Rothstein" wrote:

What is your actual Conditional Formatting formula(s)?

--
Rick (MVP - Excel)


"Robert" wrote in message
...
I use the following formulas which are conditionally formatted in column
J:

=IF(D1679="","",D1679/E1679)
=IF(D1680="","",D1680/E1680)
=IF(D1681="","",D1681/E1681)
=IF(D1682="","",D1682/E1682)
=IF(D1683="","",D1683/E1683)
=IF(D1684="","",D1684/E1684)
.

If the cell D1681 is blank, for example, the formatting on the blank
cell
fills with the color green in J1681 blank cell, and then through the
end
of
the worksheet row J2500.

These blank green cells are distracting, if the cell is blank I would
prefer
the green fill from the conditional formatting does not appear, I would
like
a blank cell. I would like the conditional formatting to apply to
cells
that
contain values only.

Do you know of a way to mask this green formatting from appearing if
the
cell value is blank?

I am currently using Excel 2007, the original worksheet was Excel
97-2003
which I "saved as" in the Excel Worksheet format.

I have literally searched for hours trying to resolve this issue, and
the
"stop if true" flag does not work. I have also read a significant
amount
of
help on conditional formatting to no avail.

Any help will be greatly appreciated.

Conditional Formatting Issue
 

You will have to change to an actual formula in your conditional format
rather than use the preset relations. Type J1681:J2500 in the Name Box
(which is the empty box to the left of the Formula Bar) so as to select the
range you want to conditionally format with J1681 as the active cell
(knowing the active cell for the selection is important since the formula we
will use will reference it directly... the Conditional Formatting dialog
will handle distributing the conditional formula to then non-active cells
automatically). Now call up the Conditional Formatting dialog and select
"Formula Is" from the first drop down, then put this formula in the next
blank field...

=AND(J1681=3,J1681<"")

and set the Format condition color (if you don't have it set any more). Now,
OK your way back to the worksheet... your cells should be colored correctly
now.

--
Rick (MVP - Excel)


"Robert" wrote in message
...
Cell Value = 3

"Rick Rothstein" wrote:

What is your actual Conditional Formatting formula(s)?

--
Rick (MVP - Excel)


"Robert" wrote in message
...
I use the following formulas which are conditionally formatted in column
J:

=IF(D1679="","",D1679/E1679)
=IF(D1680="","",D1680/E1680)
=IF(D1681="","",D1681/E1681)
=IF(D1682="","",D1682/E1682)
=IF(D1683="","",D1683/E1683)
=IF(D1684="","",D1684/E1684)
€¦

If the cell D1681 is blank, for example, the formatting on the blank
cell
fills with the color green in J1681 blank cell, and then through the
end
of
the worksheet row J2500.

These blank green cells are distracting, if the cell is blank I would
prefer
the green fill from the conditional formatting does not appear, I would
like
a blank cell. I would like the conditional formatting to apply to
cells
that
contain values only.

Do you know of a way to mask this green formatting from appearing if
the
cell value is blank?

I am currently using Excel 2007, the original worksheet was Excel
97-2003
which I €œsaved as€� in the Excel Worksheet format.

I have literally searched for hours trying to resolve this issue, and
the
€œstop if true€� flag does not work. I have also read a significant
amount
of
help on conditional formatting to no avail.

Any help will be greatly appreciated.

Conditional Formatting Issue
 

Outstanding! Rick, I greatly apprciate your help. I have a few more columns
to format I will add my replys when I am done.

Thank you,

Robert


"Rick Rothstein" wrote:

You will have to change to an actual formula in your conditional format
rather than use the preset relations. Type J1681:J2500 in the Name Box
(which is the empty box to the left of the Formula Bar) so as to select the
range you want to conditionally format with J1681 as the active cell
(knowing the active cell for the selection is important since the formula we
will use will reference it directly... the Conditional Formatting dialog
will handle distributing the conditional formula to then non-active cells
automatically). Now call up the Conditional Formatting dialog and select
"Formula Is" from the first drop down, then put this formula in the next
blank field...

=AND(J1681=3,J1681<"")

and set the Format condition color (if you don't have it set any more). Now,
OK your way back to the worksheet... your cells should be colored correctly
now.

--
Rick (MVP - Excel)


"Robert" wrote in message
...
Cell Value = 3

"Rick Rothstein" wrote:

What is your actual Conditional Formatting formula(s)?

--
Rick (MVP - Excel)


"Robert" wrote in message
...
I use the following formulas which are conditionally formatted in column
J:

=IF(D1679="","",D1679/E1679)
=IF(D1680="","",D1680/E1680)
=IF(D1681="","",D1681/E1681)
=IF(D1682="","",D1682/E1682)
=IF(D1683="","",D1683/E1683)
=IF(D1684="","",D1684/E1684)
€¦

If the cell D1681 is blank, for example, the formatting on the blank
cell
fills with the color green in J1681 blank cell, and then through the
end
of
the worksheet row J2500.

These blank green cells are distracting, if the cell is blank I would
prefer
the green fill from the conditional formatting does not appear, I would
like
a blank cell. I would like the conditional formatting to apply to
cells
that
contain values only.

Do you know of a way to mask this green formatting from appearing if
the
cell value is blank?

I am currently using Excel 2007, the original worksheet was Excel
97-2003
which I €œsaved as€� in the Excel Worksheet format.

I have literally searched for hours trying to resolve this issue, and
the
€œstop if true€� flag does not work. I have also read a significant
amount
of
help on conditional formatting to no avail.

Any help will be greatly appreciated.

Conditional Formatting Issue
 

Dear Rick,

What would the formula below look like if the conditional value is between a
couple of numbers?

=AND(J1681=3,J1681<"")

for example between 675 and 800, or between -50 and -100?

Robert

"Rick Rothstein" wrote:

You will have to change to an actual formula in your conditional format
rather than use the preset relations. Type J1681:J2500 in the Name Box
(which is the empty box to the left of the Formula Bar) so as to select the
range you want to conditionally format with J1681 as the active cell
(knowing the active cell for the selection is important since the formula we
will use will reference it directly... the Conditional Formatting dialog
will handle distributing the conditional formula to then non-active cells
automatically). Now call up the Conditional Formatting dialog and select
"Formula Is" from the first drop down, then put this formula in the next
blank field...

=AND(J1681=3,J1681<"")

and set the Format condition color (if you don't have it set any more). Now,
OK your way back to the worksheet... your cells should be colored correctly
now.

--
Rick (MVP - Excel)


"Robert" wrote in message
...
Cell Value = 3

"Rick Rothstein" wrote:

What is your actual Conditional Formatting formula(s)?

--
Rick (MVP - Excel)


"Robert" wrote in message
...
I use the following formulas which are conditionally formatted in column
J:

=IF(D1679="","",D1679/E1679)
=IF(D1680="","",D1680/E1680)
=IF(D1681="","",D1681/E1681)
=IF(D1682="","",D1682/E1682)
=IF(D1683="","",D1683/E1683)
=IF(D1684="","",D1684/E1684)
€¦

If the cell D1681 is blank, for example, the formatting on the blank
cell
fills with the color green in J1681 blank cell, and then through the
end
of
the worksheet row J2500.

These blank green cells are distracting, if the cell is blank I would
prefer
the green fill from the conditional formatting does not appear, I would
like
a blank cell. I would like the conditional formatting to apply to
cells
that
contain values only.

Do you know of a way to mask this green formatting from appearing if
the
cell value is blank?

I am currently using Excel 2007, the original worksheet was Excel
97-2003
which I €œsaved as€� in the Excel Worksheet format.

I have literally searched for hours trying to resolve this issue, and
the
€œstop if true€� flag does not work. I have also read a significant
amount
of
help on conditional formatting to no avail.

Any help will be greatly appreciated.

Conditional Formatting Issue
 

=AND(J1681675,J1681<800,J1681<"")

=AND(J1681-100,J1681<-50,J1681<"")

--
Rick (MVP - Excel)


"Robert" wrote in message
...
Dear Rick,

What would the formula below look like if the conditional value is between
a
couple of numbers?

=AND(J1681=3,J1681<"")

for example between 675 and 800, or between -50 and -100?

Robert

"Rick Rothstein" wrote:

You will have to change to an actual formula in your conditional format
rather than use the preset relations. Type J1681:J2500 in the Name Box
(which is the empty box to the left of the Formula Bar) so as to select
the
range you want to conditionally format with J1681 as the active cell
(knowing the active cell for the selection is important since the formula
we
will use will reference it directly... the Conditional Formatting dialog
will handle distributing the conditional formula to then non-active cells
automatically). Now call up the Conditional Formatting dialog and select
"Formula Is" from the first drop down, then put this formula in the next
blank field...

=AND(J1681=3,J1681<"")

and set the Format condition color (if you don't have it set any more).
Now,
OK your way back to the worksheet... your cells should be colored
correctly
now.

--
Rick (MVP - Excel)


"Robert" wrote in message
...
Cell Value = 3

"Rick Rothstein" wrote:

What is your actual Conditional Formatting formula(s)?

--
Rick (MVP - Excel)


"Robert" wrote in message
...
I use the following formulas which are conditionally formatted in
column
J:

=IF(D1679="","",D1679/E1679)
=IF(D1680="","",D1680/E1680)
=IF(D1681="","",D1681/E1681)
=IF(D1682="","",D1682/E1682)
=IF(D1683="","",D1683/E1683)
=IF(D1684="","",D1684/E1684)
€¦

If the cell D1681 is blank, for example, the formatting on the blank
cell
fills with the color green in J1681 blank cell, and then through the
end
of
the worksheet row J2500.

These blank green cells are distracting, if the cell is blank I
would
prefer
the green fill from the conditional formatting does not appear, I
would
like
a blank cell. I would like the conditional formatting to apply to
cells
that
contain values only.

Do you know of a way to mask this green formatting from appearing if
the
cell value is blank?

I am currently using Excel 2007, the original worksheet was Excel
97-2003
which I €œsaved as€� in the Excel Worksheet format.

I have literally searched for hours trying to resolve this issue,
and
the
€œstop if true€� flag does not work. I have also read a significant
amount
of
help on conditional formatting to no avail.

Any help will be greatly appreciated.

Conditional Formatting Issue
 

Excellent Rick, thank you very much you saved me a lot of grief and I truly
appreciate it, when I learn how to rate a post your posts will be 5 stars!

"Rick Rothstein" wrote:

=AND(J1681675,J1681<800,J1681<"")

=AND(J1681-100,J1681<-50,J1681<"")

--
Rick (MVP - Excel)


"Robert" wrote in message
...
Dear Rick,

What would the formula below look like if the conditional value is between
a
couple of numbers?

=AND(J1681=3,J1681<"")

for example between 675 and 800, or between -50 and -100?

Robert

"Rick Rothstein" wrote:

You will have to change to an actual formula in your conditional format
rather than use the preset relations. Type J1681:J2500 in the Name Box
(which is the empty box to the left of the Formula Bar) so as to select
the
range you want to conditionally format with J1681 as the active cell
(knowing the active cell for the selection is important since the formula
we
will use will reference it directly... the Conditional Formatting dialog
will handle distributing the conditional formula to then non-active cells
automatically). Now call up the Conditional Formatting dialog and select
"Formula Is" from the first drop down, then put this formula in the next
blank field...

=AND(J1681=3,J1681<"")

and set the Format condition color (if you don't have it set any more).
Now,
OK your way back to the worksheet... your cells should be colored
correctly
now.

--
Rick (MVP - Excel)


"Robert" wrote in message
...
Cell Value = 3

"Rick Rothstein" wrote:

What is your actual Conditional Formatting formula(s)?

--
Rick (MVP - Excel)


"Robert" wrote in message
...
I use the following formulas which are conditionally formatted in
column
J:

=IF(D1679="","",D1679/E1679)
=IF(D1680="","",D1680/E1680)
=IF(D1681="","",D1681/E1681)
=IF(D1682="","",D1682/E1682)
=IF(D1683="","",D1683/E1683)
=IF(D1684="","",D1684/E1684)
€¦

If the cell D1681 is blank, for example, the formatting on the blank
cell
fills with the color green in J1681 blank cell, and then through the
end
of
the worksheet row J2500.

These blank green cells are distracting, if the cell is blank I
would
prefer
the green fill from the conditional formatting does not appear, I
would
like
a blank cell. I would like the conditional formatting to apply to
cells
that
contain values only.

Do you know of a way to mask this green formatting from appearing if
the
cell value is blank?

I am currently using Excel 2007, the original worksheet was Excel
97-2003
which I €œsaved as€� in the Excel Worksheet format.

I have literally searched for hours trying to resolve this issue,
and
the
€œstop if true€� flag does not work. I have also read a significant
amount
of
help on conditional formatting to no avail.

Any help will be greatly appreciated.