Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Two Conditional Formats
The first conditional format is "=MOD(ROW(),2)=0". This auto shades every
other row and fixs the rows color so if I do a sort the rows will still be shaded every other row. The second Conditional Format is to color the font red if less than 0. However, if a negitive number is in a row that has been shaded, the font will not trun red, in the other rows the negatives are red. What can I do to have both the shaded cell and red negative numbers? As always, any help is very much appreciated. |
#2
|
|||
|
|||
"Ronbo" wrote: The first conditional format is "=MOD(ROW(),2)=0". This auto shades every other row and fixs the rows color so if I do a sort the rows will still be shaded every other row. The second Conditional Format is to color the font red if less than 0. However, if a negitive number is in a row that has been shaded, the font will not trun red, in the other rows the negatives are red. What can I do to have both the shaded cell and red negative numbers? As always, any help is very much appreciated. I just noticed that it works correctly for (Dollars) but not (Percentage) ??? |
#3
|
|||
|
|||
Make the first conditional format be both the mod shade and the less than
zero red font. then the other two conditionals be the mod and the less than zero "Ronbo" wrote: The first conditional format is "=MOD(ROW(),2)=0". This auto shades every other row and fixs the rows color so if I do a sort the rows will still be shaded every other row. The second Conditional Format is to color the font red if less than 0. However, if a negitive number is in a row that has been shaded, the font will not trun red, in the other rows the negatives are red. What can I do to have both the shaded cell and red negative numbers? As always, any help is very much appreciated. |
#4
|
|||
|
|||
Ronbo,
You need 3 CF's. The first should be =AND(MOD(ROW(),2)=0,A1<0) formatted red font, shaded background. Then your other two as before. HTH, Bernie MS Excel MVP "Ronbo" wrote in message ... The first conditional format is "=MOD(ROW(),2)=0". This auto shades every other row and fixs the rows color so if I do a sort the rows will still be shaded every other row. The second Conditional Format is to color the font red if less than 0. However, if a negitive number is in a row that has been shaded, the font will not trun red, in the other rows the negatives are red. What can I do to have both the shaded cell and red negative numbers? As always, any help is very much appreciated. |
#5
|
|||
|
|||
Thanks to both for the help. However, I am not getting it to work. It takes
all shading out and changes the font on rows without shading. Same as before. I did it twice and checked each time for a grammer errors. I am conditionally formating columns. Any ideas what I might be doing wrong? "Bernie Deitrick" wrote: Ronbo, You need 3 CF's. The first should be =AND(MOD(ROW(),2)=0,A1<0) formatted red font, shaded background. Then your other two as before. HTH, Bernie MS Excel MVP "Ronbo" wrote in message ... The first conditional format is "=MOD(ROW(),2)=0". This auto shades every other row and fixs the rows color so if I do a sort the rows will still be shaded every other row. The second Conditional Format is to color the font red if less than 0. However, if a negitive number is in a row that has been shaded, the font will not trun red, in the other rows the negatives are red. What can I do to have both the shaded cell and red negative numbers? As always, any help is very much appreciated. |
#6
|
|||
|
|||
when you are in, say C3, what does the conditional formating say for each
condition? additionally, what do you mean when you say you are formatting columns? "Ronbo" wrote: Thanks to both for the help. However, I am not getting it to work. It takes all shading out and changes the font on rows without shading. Same as before. I did it twice and checked each time for a grammer errors. I am conditionally formating columns. Any ideas what I might be doing wrong? "Bernie Deitrick" wrote: Ronbo, You need 3 CF's. The first should be =AND(MOD(ROW(),2)=0,A1<0) formatted red font, shaded background. Then your other two as before. HTH, Bernie MS Excel MVP "Ronbo" wrote in message ... The first conditional format is "=MOD(ROW(),2)=0". This auto shades every other row and fixs the rows color so if I do a sort the rows will still be shaded every other row. The second Conditional Format is to color the font red if less than 0. However, if a negitive number is in a row that has been shaded, the font will not trun red, in the other rows the negatives are red. What can I do to have both the shaded cell and red negative numbers? As always, any help is very much appreciated. |
#7
|
|||
|
|||
Lets use h3, h4
h3= Condition 1 =MOD(ROW(),2)=0,H3<0 Condition 2 =MOD(ROW(),2)=0 Condition 3 CellValue - less than - 0 - Format = Red Fonts I am Conditional Formating a column at a time i.e. Highlight column H and put in CF. Each of the references in H1 change to the appropriate cell so in cell h4 it would be =MOD(ROW(),2)=0,H4<0 "bj" wrote: when you are in, say C3, what does the conditional formating say for each condition? additionally, what do you mean when you say you are formatting columns? "Ronbo" wrote: Thanks to both for the help. However, I am not getting it to work. It takes all shading out and changes the font on rows without shading. Same as before. I did it twice and checked each time for a grammer errors. I am conditionally formating columns. Any ideas what I might be doing wrong? "Bernie Deitrick" wrote: Ronbo, You need 3 CF's. The first should be =AND(MOD(ROW(),2)=0,A1<0) formatted red font, shaded background. Then your other two as before. HTH, Bernie MS Excel MVP "Ronbo" wrote in message ... The first conditional format is "=MOD(ROW(),2)=0". This auto shades every other row and fixs the rows color so if I do a sort the rows will still be shaded every other row. The second Conditional Format is to color the font red if less than 0. However, if a negitive number is in a row that has been shaded, the font will not trun red, in the other rows the negatives are red. What can I do to have both the shaded cell and red negative numbers? As always, any help is very much appreciated. |
#8
|
|||
|
|||
Yes, you need three CFs.
CF1: =AND(A1<0,MOD(ROW(),2)=0) (shade cell and red font) CF2: =AND(A1=0,MOD(ROW(),2)=0) (shade cell and black font) CF3: =AND(A1<0,MOD(ROW(),2)=1) (no shade and red font) Regards, B.R.Ramachandran "Ronbo" wrote: The first conditional format is "=MOD(ROW(),2)=0". This auto shades every other row and fixs the rows color so if I do a sort the rows will still be shaded every other row. The second Conditional Format is to color the font red if less than 0. However, if a negitive number is in a row that has been shaded, the font will not trun red, in the other rows the negatives are red. What can I do to have both the shaded cell and red negative numbers? As always, any help is very much appreciated. |
#9
|
|||
|
|||
You left out the AND:
=AND(MOD(ROW(),2)=0,H3<0) HTH, Bernie MS Excel MVP "Ronbo" wrote in message ... Lets use h3, h4 h3= Condition 1 =MOD(ROW(),2)=0,H3<0 Condition 2 =MOD(ROW(),2)=0 Condition 3 CellValue - less than - 0 - Format = Red Fonts I am Conditional Formating a column at a time i.e. Highlight column H and put in CF. Each of the references in H1 change to the appropriate cell so in cell h4 it would be =MOD(ROW(),2)=0,H4<0 "bj" wrote: when you are in, say C3, what does the conditional formating say for each condition? additionally, what do you mean when you say you are formatting columns? "Ronbo" wrote: Thanks to both for the help. However, I am not getting it to work. It takes all shading out and changes the font on rows without shading. Same as before. I did it twice and checked each time for a grammer errors. I am conditionally formating columns. Any ideas what I might be doing wrong? "Bernie Deitrick" wrote: Ronbo, You need 3 CF's. The first should be =AND(MOD(ROW(),2)=0,A1<0) formatted red font, shaded background. Then your other two as before. HTH, Bernie MS Excel MVP "Ronbo" wrote in message ... The first conditional format is "=MOD(ROW(),2)=0". This auto shades every other row and fixs the rows color so if I do a sort the rows will still be shaded every other row. The second Conditional Format is to color the font red if less than 0. However, if a negitive number is in a row that has been shaded, the font will not trun red, in the other rows the negatives are red. What can I do to have both the shaded cell and red negative numbers? As always, any help is very much appreciated. |
#10
|
|||
|
|||
B. R.Ramachandran
Thanks for your help, however it did not work. It stripped the "Alternative Row Shading" (*1) away. What I did was highlighted rows 20:100 then did a conditional format of; =MOD(ROW(),2)=0 (*1) This works perfect. It alternatively shades rows the color you want and it fixes the color to the row so that when sorting/adding/deleting it will still have alternatively shaded rows. I want to shade all negitive numbers red. For dollars I "Format Cells" with Dollars - (red) and it works perfect. But with percentage that option is not available. So I have been trying conditional formating and the above suggestions, but nothing works for percentage. Again any help is appreciated. (*1) The code is from John Walenback at j-walk.com. Sorry I did not recognize this before, but I did not have the programmers name. "B. R.Ramachandran" wrote: Yes, you need three CFs. CF1: =AND(A1<0,MOD(ROW(),2)=0) (shade cell and red font) CF2: =AND(A1=0,MOD(ROW(),2)=0) (shade cell and black font) CF3: =AND(A1<0,MOD(ROW(),2)=1) (no shade and red font) Regards, B.R.Ramachandran "Ronbo" wrote: The first conditional format is "=MOD(ROW(),2)=0". This auto shades every other row and fixs the rows color so if I do a sort the rows will still be shaded every other row. The second Conditional Format is to color the font red if less than 0. However, if a negitive number is in a row that has been shaded, the font will not trun red, in the other rows the negatives are red. What can I do to have both the shaded cell and red negative numbers? As always, any help is very much appreciated. |
#11
|
|||
|
|||
Hi Ronbo,
I actually tested the CF formulas in a trial Excel spreadsheet before posting my suggestion to you. The CF works (shades alternate rows, and also shows negative values in red font regardless of whether they are in shaded rows or not; furthermore, it works regardless of whether the cell contents are formatted as number, currency, or percentage). I am giving the formulas again. In the Conditional Formatting window, select the 'Formula Is' (and not the 'Cell Value Is') option. For condition 1, the rows are shaded and the font is colored red (or whatever color you want); for condition 2, the rows are shaded but the font color is the default color (black); and for condition 3, there is no shading for the rows but the font is colored red. Conditn 1: Formula Is =AND(A1<0,MOD(ROW(),2)=0) Conditn 2: Formula Is =AND(A1=0,MOD(ROW(),2)=0) Conditn 3: Formula Is =AND(A1<0,MOD(ROW(),2)=1) Regards, B.R. Ramachandran "Ronbo" wrote: B. R.Ramachandran Thanks for your help, however it did not work. It stripped the "Alternative Row Shading" (*1) away. What I did was highlighted rows 20:100 then did a conditional format of; =MOD(ROW(),2)=0 (*1) This works perfect. It alternatively shades rows the color you want and it fixes the color to the row so that when sorting/adding/deleting it will still have alternatively shaded rows. I want to shade all negitive numbers red. For dollars I "Format Cells" with Dollars - (red) and it works perfect. But with percentage that option is not available. So I have been trying conditional formating and the above suggestions, but nothing works for percentage. Again any help is appreciated. (*1) The code is from John Walenback at j-walk.com. Sorry I did not recognize this before, but I did not have the programmers name. "B. R.Ramachandran" wrote: Yes, you need three CFs. CF1: =AND(A1<0,MOD(ROW(),2)=0) (shade cell and red font) CF2: =AND(A1=0,MOD(ROW(),2)=0) (shade cell and black font) CF3: =AND(A1<0,MOD(ROW(),2)=1) (no shade and red font) Regards, B.R.Ramachandran "Ronbo" wrote: The first conditional format is "=MOD(ROW(),2)=0". This auto shades every other row and fixs the rows color so if I do a sort the rows will still be shaded every other row. The second Conditional Format is to color the font red if less than 0. However, if a negitive number is in a row that has been shaded, the font will not trun red, in the other rows the negatives are red. What can I do to have both the shaded cell and red negative numbers? As always, any help is very much appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
list all conditional formats | Excel Worksheet Functions | |||
How do I add more Conditional Formats? | Excel Discussion (Misc queries) | |||
how do I apply more than 3 conditional formats in excel | Excel Discussion (Misc queries) | |||
how do i get more than three conditional formats in excel | Excel Worksheet Functions | |||
Conditional Formats in Excel | Excel Worksheet Functions |