software

Hi there,

I've applied Formula is =MOD(ROW(),2)=1 and selected a shading colour for my
table. However, if the cell values = 0, I want the numbers to be red.

Can't seem to get it to work, as when both conditions are true, the second
one gets ignored. Perhaps it's to do with OR/ AND statements?

Help appreciated!

Thanks,
Debbie


Try 3 conditions,

first = AND( row , values) -- shading and red font
second = rows -- shading
third = values -- red font

Hope this helps

--

Smurfette Wrote:
gt; Hi there,
gt;
gt; I've applied Formula is =MOD(ROW(),2)=1 and selected a shading colour
gt; for my
gt; table. However, if the cell values = 0, I want the numbers to be red.
gt;
gt; Can't seem to get it to work, as when both conditions are true, the
gt; second
gt; one gets ignored. Perhaps it's to do with OR/ AND statements?
gt;
gt; Help appreciated!
gt;
gt; Thanks,
gt; Debbie--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: www.excelforum.com/member.php...oamp;userid=21059
View this thread: www.excelforum.com/showthread...hreadid=536233Hi!

AND/OR will not help in this case.

You have 2 distinct condtions and as you've discovered only one condition
can be satisfied.

You could use the cf for the row banding and then use a custom format for
cells:

0;-0;[Red]General

Biff

quot;Smurfettequot; gt; wrote in message
...
gt; Hi there,
gt;
gt; I've applied Formula is =MOD(ROW(),2)=1 and selected a shading colour for
gt; my
gt; table. However, if the cell values = 0, I want the numbers to be red.
gt;
gt; Can't seem to get it to work, as when both conditions are true, the second
gt; one gets ignored. Perhaps it's to do with OR/ AND statements?
gt;
gt; Help appreciated!
gt;
gt; Thanks,
gt; Debbie
Yes, that does work!

Biff

quot;Bryan Hesseyquot; gt;
wrote in message
news:Bryan.Hessey.26unrm_1146018300.9619@excelforu m-nospam.com...
gt;
gt; Try 3 conditions,
gt;
gt; first = AND( row , values) -- shading and red font
gt; second = rows -- shading
gt; third = values -- red font
gt;
gt; Hope this helps
gt;
gt; --
gt;
gt; Smurfette Wrote:
gt;gt; Hi there,
gt;gt;
gt;gt; I've applied Formula is =MOD(ROW(),2)=1 and selected a shading colour
gt;gt; for my
gt;gt; table. However, if the cell values = 0, I want the numbers to be red.
gt;gt;
gt;gt; Can't seem to get it to work, as when both conditions are true, the
gt;gt; second
gt;gt; one gets ignored. Perhaps it's to do with OR/ AND statements?
gt;gt;
gt;gt; Help appreciated!
gt;gt;
gt;gt; Thanks,
gt;gt; Debbie
gt;
gt;
gt; --
gt; Bryan Hessey
gt; ------------------------------------------------------------------------
gt; Bryan Hessey's Profile:
gt; www.excelforum.com/member.php...oamp;userid=21059
gt; View this thread: www.excelforum.com/showthread...hreadid=536233
gt;
Thanks for your help, Biff and Bryan.

Biff, your method works a treat. Bryan, I am interested in getting yours
right too, for the learning. I tried something similar to what you proposed,
but got stuck in the first condition when specifying the value. For example:

1. Formula Is: =AND(MOD(ROW(),2)=1,CellValueIs=O)

How do I write the quot;Cell Value Isquot; part, so that I don't have to specify an
actual cell (eg A10=0), but rather refers to the cell being formatted?
Sorry, could be very simple...I discovered conditional formatting about 2
hours ago!

Many thanks,
Deb

quot;Biffquot; wrote:

gt; Yes, that does work!
gt;
gt; Biff
gt;
gt; quot;Bryan Hesseyquot; gt;
gt; wrote in message
gt; news:Bryan.Hessey.26unrm_1146018300.9619@excelforu m-nospam.com...
gt; gt;
gt; gt; Try 3 conditions,
gt; gt;
gt; gt; first = AND( row , values) -- shading and red font
gt; gt; second = rows -- shading
gt; gt; third = values -- red font
gt; gt;
gt; gt; Hope this helps
gt; gt;
gt; gt; --
gt; gt;
gt; gt; Smurfette Wrote:
gt; gt;gt; Hi there,
gt; gt;gt;
gt; gt;gt; I've applied Formula is =MOD(ROW(),2)=1 and selected a shading colour
gt; gt;gt; for my
gt; gt;gt; table. However, if the cell values = 0, I want the numbers to be red.
gt; gt;gt;
gt; gt;gt; Can't seem to get it to work, as when both conditions are true, the
gt; gt;gt; second
gt; gt;gt; one gets ignored. Perhaps it's to do with OR/ AND statements?
gt; gt;gt;
gt; gt;gt; Help appreciated!
gt; gt;gt;
gt; gt;gt; Thanks,
gt; gt;gt; Debbie
gt; gt;
gt; gt;
gt; gt; --
gt; gt; Bryan Hessey
gt; gt; ------------------------------------------------------------------------
gt; gt; Bryan Hessey's Profile:
gt; gt; www.excelforum.com/member.php...oamp;userid=21059
gt; gt; View this thread: www.excelforum.com/showthread...hreadid=536233
gt; gt;
gt;
gt;
gt;


Hi,
and Hi Biff,

with the required range selected, and the first cell (A1 in my formula)
the active cell (the odd colour highlight)

in the first condition - formula
=AND(MOD(ROW(),2)=1,A1=0)

in the second condition - formula
=(A1=0)

in the third condition - formula
=MOD(ROW(),2

Seems to work for zero = (font red) and/or row = odd (pattern =
colour)

Hope this helps

--

Smurfette Wrote:
gt; Thanks for your help, Biff and Bryan.
gt;
gt; Biff, your method works a treat. Bryan, I am interested in getting
gt; yours
gt; right too, for the learning. I tried something similar to what you
gt; proposed,
gt; but got stuck in the first condition when specifying the value. For
gt; example:
gt;
gt; 1. Formula Is: =AND(MOD(ROW(),2)=1,CellValueIs=O)
gt;
gt; How do I write the quot;Cell Value Isquot; part, so that I don't have to
gt; specify an
gt; actual cell (eg A10=0), but rather refers to the cell being formatted?
gt; Sorry, could be very simple...I discovered conditional formatting about
gt; 2
gt; hours ago!
gt;
gt; Many thanks,
gt; Deb
gt;
gt; quot;Biffquot; wrote:
gt;
gt; gt; Yes, that does work!
gt; gt;
gt; gt; Biff
gt; gt;
gt; gt; quot;Bryan Hesseyquot;
gt; gt;
gt; gt; wrote in message
gt; gt; news:Bryan.Hessey.26unrm_1146018300.9619@excelforu m-nospam.com...
gt; gt; gt;
gt; gt; gt; Try 3 conditions,
gt; gt; gt;
gt; gt; gt; first = AND( row , values) -- shading and red font
gt; gt; gt; second = rows -- shading
gt; gt; gt; third = values -- red font
gt; gt; gt;
gt; gt; gt; Hope this helps
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt;
gt; gt; gt; Smurfette Wrote:
gt; gt; gt;gt; Hi there,
gt; gt; gt;gt;
gt; gt; gt;gt; I've applied Formula is =MOD(ROW(),2)=1 and selected a shading
gt; colour
gt; gt; gt;gt; for my
gt; gt; gt;gt; table. However, if the cell values = 0, I want the numbers to be
gt; red.
gt; gt; gt;gt;
gt; gt; gt;gt; Can't seem to get it to work, as when both conditions are true,
gt; the
gt; gt; gt;gt; second
gt; gt; gt;gt; one gets ignored. Perhaps it's to do with OR/ AND statements?
gt; gt; gt;gt;
gt; gt; gt;gt; Help appreciated!
gt; gt; gt;gt;
gt; gt; gt;gt; Thanks,
gt; gt; gt;gt; Debbie
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; Bryan Hessey
gt; gt; gt;
gt; ------------------------------------------------------------------------
gt; gt; gt; Bryan Hessey's Profile:
gt; gt; gt; www.excelforum.com/member.php...oamp;userid=21059
gt; gt; gt; View this thread:
gt; www.excelforum.com/showthread...hreadid=536233
gt; gt; gt;
gt; gt;
gt; gt;
gt; gt;--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: www.excelforum.com/member.php...oamp;userid=21059
View this thread: www.excelforum.com/showthread...hreadid=536233Got it, thanks for that! I missed the (simple) link that the cell reference
is relative and will be transfered to all cells in the range, when the
conditional formatting is applied.

Cheers,
Debbie

quot;Bryan Hesseyquot; wrote:

gt;
gt; Hi,
gt; and Hi Biff,
gt;
gt; with the required range selected, and the first cell (A1 in my formula)
gt; the active cell (the odd colour highlight)
gt;
gt; in the first condition - formula
gt; =AND(MOD(ROW(),2)=1,A1=0)
gt;
gt; in the second condition - formula
gt; =(A1=0)
gt;
gt; in the third condition - formula
gt; =MOD(ROW(),2
gt;
gt; Seems to work for zero = (font red) and/or row = odd (pattern =
gt; colour)
gt;
gt; Hope this helps
gt;
gt; --
gt;
gt; Smurfette Wrote:
gt; gt; Thanks for your help, Biff and Bryan.
gt; gt;
gt; gt; Biff, your method works a treat. Bryan, I am interested in getting
gt; gt; yours
gt; gt; right too, for the learning. I tried something similar to what you
gt; gt; proposed,
gt; gt; but got stuck in the first condition when specifying the value. For
gt; gt; example:
gt; gt;
gt; gt; 1. Formula Is: =AND(MOD(ROW(),2)=1,CellValueIs=O)
gt; gt;
gt; gt; How do I write the quot;Cell Value Isquot; part, so that I don't have to
gt; gt; specify an
gt; gt; actual cell (eg A10=0), but rather refers to the cell being formatted?
gt; gt; Sorry, could be very simple...I discovered conditional formatting about
gt; gt; 2
gt; gt; hours ago!
gt; gt;
gt; gt; Many thanks,
gt; gt; Deb
gt; gt;
gt; gt; quot;Biffquot; wrote:
gt; gt;
gt; gt; gt; Yes, that does work!
gt; gt; gt;
gt; gt; gt; Biff
gt; gt; gt;
gt; gt; gt; quot;Bryan Hesseyquot;
gt; gt; gt;
gt; gt; gt; wrote in message
gt; gt; gt; news:Bryan.Hessey.26unrm_1146018300.9619@excelforu m-nospam.com...
gt; gt; gt; gt;
gt; gt; gt; gt; Try 3 conditions,
gt; gt; gt; gt;
gt; gt; gt; gt; first = AND( row , values) -- shading and red font
gt; gt; gt; gt; second = rows -- shading
gt; gt; gt; gt; third = values -- red font
gt; gt; gt; gt;
gt; gt; gt; gt; Hope this helps
gt; gt; gt; gt;
gt; gt; gt; gt; --
gt; gt; gt; gt;
gt; gt; gt; gt; Smurfette Wrote:
gt; gt; gt; gt;gt; Hi there,
gt; gt; gt; gt;gt;
gt; gt; gt; gt;gt; I've applied Formula is =MOD(ROW(),2)=1 and selected a shading
gt; gt; colour
gt; gt; gt; gt;gt; for my
gt; gt; gt; gt;gt; table. However, if the cell values = 0, I want the numbers to be
gt; gt; red.
gt; gt; gt; gt;gt;
gt; gt; gt; gt;gt; Can't seem to get it to work, as when both conditions are true,
gt; gt; the
gt; gt; gt; gt;gt; second
gt; gt; gt; gt;gt; one gets ignored. Perhaps it's to do with OR/ AND statements?
gt; gt; gt; gt;gt;
gt; gt; gt; gt;gt; Help appreciated!
gt; gt; gt; gt;gt;
gt; gt; gt; gt;gt; Thanks,
gt; gt; gt; gt;gt; Debbie
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; --
gt; gt; gt; gt; Bryan Hessey
gt; gt; gt; gt;
gt; gt; ------------------------------------------------------------------------
gt; gt; gt; gt; Bryan Hessey's Profile:
gt; gt; gt; gt; www.excelforum.com/member.php...oamp;userid=21059
gt; gt; gt; gt; View this thread:
gt; gt; www.excelforum.com/showthread...hreadid=536233
gt; gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt;
gt;
gt; --
gt; Bryan Hessey
gt; ------------------------------------------------------------------------
gt; Bryan Hessey's Profile: www.excelforum.com/member.php...oamp;userid=21059
gt; View this thread: www.excelforum.com/showthread...hreadid=536233
gt;
gt;


Good to see, and, from Biff and myself, thanks for the response.

--
Smurfette Wrote:
gt; Got it, thanks for that! I missed the (simple) link that the cell
gt; reference
gt; is relative and will be transfered to all cells in the range, when the
gt; conditional formatting is applied.
gt;
gt; Cheers,
gt; Debbie
gt;
gt; quot;Bryan Hesseyquot; wrote:
gt;
gt; gt;
gt; gt; Hi,
gt; gt; and Hi Biff,
gt; gt;
gt; gt; with the required range selected, and the first cell (A1 in my
gt; formula)
gt; gt; the active cell (the odd colour highlight)
gt; gt;
gt; gt; in the first condition - formula
gt; gt; =AND(MOD(ROW(),2)=1,A1=0)
gt; gt;
gt; gt; in the second condition - formula
gt; gt; =(A1=0)
gt; gt;
gt; gt; in the third condition - formula
gt; gt; =MOD(ROW(),2
gt; gt;
gt; gt; Seems to work for zero = (font red) and/or row = odd (pattern =
gt; gt; colour)
gt; gt;
gt; gt; Hope this helps
gt; gt;
gt; gt; --
gt; gt;
gt; gt; Smurfette Wrote:
gt; gt; gt; Thanks for your help, Biff and Bryan.
gt; gt; gt;
gt; gt; gt; Biff, your method works a treat. Bryan, I am interested in
gt; getting
gt; gt; gt; yours
gt; gt; gt; right too, for the learning. I tried something similar to what
gt; you
gt; gt; gt; proposed,
gt; gt; gt; but got stuck in the first condition when specifying the value.
gt; For
gt; gt; gt; example:
gt; gt; gt;
gt; gt; gt; 1. Formula Is: =AND(MOD(ROW(),2)=1,CellValueIs=O)
gt; gt; gt;
gt; gt; gt; How do I write the quot;Cell Value Isquot; part, so that I don't have to
gt; gt; gt; specify an
gt; gt; gt; actual cell (eg A10=0), but rather refers to the cell being
gt; formatted?
gt; gt; gt; Sorry, could be very simple...I discovered conditional formatting
gt; about
gt; gt; gt; 2
gt; gt; gt; hours ago!
gt; gt; gt;
gt; gt; gt; Many thanks,
gt; gt; gt; Deb
gt; gt; gt;
gt; gt; gt; quot;Biffquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Yes, that does work!
gt; gt; gt; gt;
gt; gt; gt; gt; Biff
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Bryan Hesseyquot;
gt; gt; gt; gt;
gt; gt; gt; gt; wrote in message
gt; gt; gt; gt;
gt; news:Bryan.Hessey.26unrm_1146018300.9619@excelforu m-nospam.com...
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Try 3 conditions,
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; first = AND( row , values) -- shading and red font
gt; gt; gt; gt; gt; second = rows -- shading
gt; gt; gt; gt; gt; third = values -- red font
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Hope this helps
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; --
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Smurfette Wrote:
gt; gt; gt; gt; gt;gt; Hi there,
gt; gt; gt; gt; gt;gt;
gt; gt; gt; gt; gt;gt; I've applied Formula is =MOD(ROW(),2)=1 and selected a
gt; shading
gt; gt; gt; colour
gt; gt; gt; gt; gt;gt; for my
gt; gt; gt; gt; gt;gt; table. However, if the cell values = 0, I want the numbers to
gt; be
gt; gt; gt; red.
gt; gt; gt; gt; gt;gt;
gt; gt; gt; gt; gt;gt; Can't seem to get it to work, as when both conditions are
gt; true,
gt; gt; gt; the
gt; gt; gt; gt; gt;gt; second
gt; gt; gt; gt; gt;gt; one gets ignored. Perhaps it's to do with OR/ AND
gt; statements?
gt; gt; gt; gt; gt;gt;
gt; gt; gt; gt; gt;gt; Help appreciated!
gt; gt; gt; gt; gt;gt;
gt; gt; gt; gt; gt;gt; Thanks,
gt; gt; gt; gt; gt;gt; Debbie
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; --
gt; gt; gt; gt; gt; Bryan Hessey
gt; gt; gt; gt; gt;
gt; gt; gt;
gt; ------------------------------------------------------------------------
gt; gt; gt; gt; gt; Bryan Hessey's Profile:
gt; gt; gt; gt; gt;
gt; www.excelforum.com/member.php...oamp;userid=21059
gt; gt; gt; gt; gt; View this thread:
gt; gt; gt; www.excelforum.com/showthread...hreadid=536233
gt; gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt;
gt; gt;
gt; gt; --
gt; gt; Bryan Hessey
gt; gt;
gt; ------------------------------------------------------------------------
gt; gt; Bryan Hessey's Profile:
gt; www.excelforum.com/member.php...oamp;userid=21059
gt; gt; View this thread:
gt; www.excelforum.com/showthread...hreadid=536233
gt; gt;
gt; gt;--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: www.excelforum.com/member.php...oamp;userid=21059
View this thread: www.excelforum.com/showthread...hreadid=536233