conditional formatting

[ndendrinos]

hello to all
In A1 I have an amount
I want to conditional format A2:A100 like this
If amt in A2 less than A1 fill in red ... if more than A1 fill in green
I need to extend this conditional formatting to all cells in column A (up to A100)
Many thanks for your help

I use excel 2000

 

[etaf]

setup 2 rules
select a2:a100

the in the rule

=A2 < A1
choose a red fill format

next rule

=A2 > A1
green

what if A1 = A2

change the < or > to include =
ie
< =
> =

will any cell be blank - as the will be seen as zero

so if you want to ignore blank

=AND( A2 < > "" , A2 < A1 )
red

=AND( A2 < > "" , A2 > A1 )

 

[ndendrinos]

setup 2 rules
select a2:a100

the in the rule

=A2 < A1
choose a red fill format

next rule

=A2 > A1
green

what if A1 = A2

change the < or > to include =
ie
< =
> =

will any cell be blank - as the will be seen as zero

so if you want to ignore blank

=AND( A2 < > "" , A2 < A1 )
red

=AND( A2 < > "" , A2 > A1 )

not sure how to reply but I thank you

 

[etaf]

did it work ok ?

do i need to explain better ?

maybe an example ?
except i dont have office 2000 - which will be limited

 

[Eric W]

I have a version of Excel 2000 loaded, and the Conditional Formatting is significantly different from later versions. To get your requirements to work on 2000, do this:

Select a2:a100
From the menu, click Format > Conditional Formatting
From the dialog box, select "Formula Is" (replacing "Cell Value Is")
In the formula box, type: =A2< A1<a1
Click Format, and choose the Patterns tab, then choose a red color

Repeat those steps, changing the formula to =A2>A1 and choose a green color.

Hope this helps.</a1

 

[Eric W]

To expand a bit on how to add the second rule: with the dialog box still open after choosing the red color, click the Add >> button on the bottom, then follow the same steps to add the second rule. Excel 2000 only allows 3 CF rules for a given range.