Conditional Filtering

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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)
 
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.
 
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.
 
Novel approach, using VBA to do what CF does very easily.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
Back
Top