| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional formatting: alternate shading PLUS red for 0 values
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional formatting: alternate shading PLUS red for 0 values
Try 3 conditions, first = AND( row , values) -- shading and red font second = rows -- shading third = values -- red font Hope this helps -- Smurfette Wrote: 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 -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=536233 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional formatting: alternate shading PLUS red for 0 values
Yes, that does work!
Biff "Bryan Hessey" wrote in message news:Bryan.Hessey.26unrm_1146018300.9619@excelforu m-nospam.com... Try 3 conditions, first = AND( row , values) -- shading and red font second = rows -- shading third = values -- red font Hope this helps -- Smurfette Wrote: 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 -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=536233 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional formatting: alternate shading PLUS red for 0 value
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 "Cell Value Is" 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 "Biff" wrote: Yes, that does work! Biff "Bryan Hessey" wrote in message news:Bryan.Hessey.26unrm_1146018300.9619@excelforu m-nospam.com... Try 3 conditions, first = AND( row , values) -- shading and red font second = rows -- shading third = values -- red font Hope this helps -- Smurfette Wrote: 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 -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=536233 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional formatting: alternate shading PLUS red for 0 values
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: 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 "Cell Value Is" 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 "Biff" wrote: Yes, that does work! Biff "Bryan Hessey" wrote in message news:Bryan.Hessey.26unrm_1146018300.9619@excelforu m-nospam.com... Try 3 conditions, first = AND( row , values) -- shading and red font second = rows -- shading third = values -- red font Hope this helps -- Smurfette Wrote: 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 -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=536233 -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=536233 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional formatting: alternate shading PLUS red for 0 value
Got 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 "Bryan Hessey" wrote: 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: 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 "Cell Value Is" 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 "Biff" wrote: Yes, that does work! Biff "Bryan Hessey" wrote in message news:Bryan.Hessey.26unrm_1146018300.9619@excelforu m-nospam.com... Try 3 conditions, first = AND( row , values) -- shading and red font second = rows -- shading third = values -- red font Hope this helps -- Smurfette Wrote: 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 -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=536233 -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=536233 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional formatting: alternate shading PLUS red for 0 values
Hi!
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 "Smurfette" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Conditional Formatting: "handwritten" circles? | Excel Discussion (Misc queries) | |||
| Conditional Formatting when inserting a row | Excel Worksheet Functions | |||
| difficulty with conditional formatting | Excel Discussion (Misc queries) | |||
| conditional formatting question | Excel Discussion (Misc queries) | |||
| Conditional formatting not available in Excel | Excel Discussion (Misc queries) | |||
Hybrid Mode
