Two Conditional Formats

[Ronbo]

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.

[Ronbo]

"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)
???

[bj]

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.

[Bernie Deitrick]

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" <Ronbo@discussions.microsoft.com> wrote in message
news:EEB9EB84-F662-4D03-97C5-4E6A839617EF@microsoft.com...
> 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.

[Ronbo]

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" <Ronbo@discussions.microsoft.com> wrote in message
> news:EEB9EB84-F662-4D03-97C5-4E6A839617EF@microsoft.com...
> > 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.
>
>
>

[bj]

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" <Ronbo@discussions.microsoft.com> wrote in message
> > news:EEB9EB84-F662-4D03-97C5-4E6A839617EF@microsoft.com...
> > > 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.
> >
> >
> >

[Ronbo]

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" <Ronbo@discussions.microsoft.com> wrote in message
> > > news:EEB9EB84-F662-4D03-97C5-4E6A839617EF@microsoft.com...
> > > > 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.
> > >
> > >
> > >

[B. R.Ramachandran]

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.

[Bernie Deitrick]

You left out the AND:

=AND(MOD(ROW(),2)=0,H3<0)

HTH,
Bernie
MS Excel MVP

"Ronbo" <Ronbo@discussions.microsoft.com> wrote in message
news:9955F6C9-BF24-48C8-B8BC-A411DDC32CEB@microsoft.com...
> 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" <Ronbo@discussions.microsoft.com> wrote in message
>> > > news:EEB9EB84-F662-4D03-97C5-4E6A839617EF@microsoft.com...
>> > > > 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.
>> > >
>> > >
>> > >

[Ronbo]

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.

[B. R.Ramachandran]

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.