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.