Conditional Filtering:for different color

[carl]

I have these 3 conditions I am trying to set my conditional filtering for:

1. if A1<C1 and A1<>0 color the cell green
2. if A1>C1 and A1<>0 color the cell blue
3. if A1=0 color the cell black

Thank you in advance.

[Bob Phillips]

It is conditional formatting, not filtering.

You have the formulae, all you need to do is change Condition 1 (and 2 and
3) to Formula Is in CF then plug in each formula.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"carl" <carl@discussions.microsoft.com> wrote in message
news:D8EFF706-12D7-4DEA-93BA-FB2535E0D267@microsoft.com...
> I have these 3 conditions I am trying to set my conditional filtering for:
>
> 1. if A1<C1 and A1<>0 color the cell green
> 2. if A1>C1 and A1<>0 color the cell blue
> 3. if A1=0 color the cell black
>
> Thank you in advance.

[Debra Dalgleish]

To simplify the formulas, make #3 the first condition:

Cell Value Is equal to 0

Condition 2 (green): Formula Is =A1<C1

Condition 3 (blue): Formula Is =A1>C1

If A1=C1, the cell won't be coloured.

carl wrote:
> I have these 3 conditions I am trying to set my conditional filtering for:
>
> 1. if A1<C1 and A1<>0 color the cell green
> 2. if A1>C1 and A1<>0 color the cell blue
> 3. if A1=0 color the cell black
>
> Thank you in advance.


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

[Martin]

I don't think conditional formatting's that clever.

Here's a macro that'll do the job - just make sure you select all the values
in column A before you run it:

Sub myConditionalFormat()
Dim myCell As Range
For Each myCell In Selection
If myCell.Value < myCell.Offset(0, 2).Value And myCell.Value <> 0 Then
myCell.Interior.ColorIndex = 4
ElseIf myCell.Value > myCell.Offset(0, 2).Value And myCell.Value <>
0 Then
myCell.Interior.ColorIndex = 5
ElseIf myCell.Value = 0 Then
myCell.Interior.ColorIndex = 1
End If
Next
End Sub

If you haven't done macros before, go to the VB Editor (from Tools, Macro),
click on Insert, Module and then paste the above into it. To run it, select
your values in column A and then click Tools, Macro, Macros and double-click
myConditionalFormat in the list.


"carl" wrote:

> I have these 3 conditions I am trying to set my conditional filtering for:
>
> 1. if A1<C1 and A1<>0 color the cell green
> 2. if A1>C1 and A1<>0 color the cell blue
> 3. if A1=0 color the cell black
>
> Thank you in advance.

[Bob Phillips]

Novel approach, using VBA to do what CF does very easily.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Martin" <Martin@discussions.microsoft.com> wrote in message
news:A2F302F1-0224-4D69-BFEB-A6C02647D8ED@microsoft.com...
> I don't think conditional formatting's that clever.
>
> Here's a macro that'll do the job - just make sure you select all the
values
> in column A before you run it:
>
> Sub myConditionalFormat()
> Dim myCell As Range
> For Each myCell In Selection
> If myCell.Value < myCell.Offset(0, 2).Value And myCell.Value <> 0
Then
> myCell.Interior.ColorIndex = 4
> ElseIf myCell.Value > myCell.Offset(0, 2).Value And myCell.Value
<>
> 0 Then
> myCell.Interior.ColorIndex = 5
> ElseIf myCell.Value = 0 Then
> myCell.Interior.ColorIndex = 1
> End If
> Next
> End Sub
>
> If you haven't done macros before, go to the VB Editor (from Tools,
Macro),
> click on Insert, Module and then paste the above into it. To run it,
select
> your values in column A and then click Tools, Macro, Macros and
double-click
> myConditionalFormat in the list.
>
>
> "carl" wrote:
>
> > I have these 3 conditions I am trying to set my conditional filtering
for:
> >
> > 1. if A1<C1 and A1<>0 color the cell green
> > 2. if A1>C1 and A1<>0 color the cell blue
> > 3. if A1=0 color the cell black
> >
> > Thank you in advance.