Conditional Formatting the whole column

[simon]

Is it possible to apply Conditional Formatting to cells in
a column, each with there own formula.

dog 1 2
cat 2 5
cow 5 3
rat 13 1

I would like to highlight each cell in the first column
red or blue depending on which of the two number columns
is greater.
Red if the 3rd column is greater and blue is the second
column is greater.

Results:

dog - blue
cat - blue
cow - red
rat - red

Thank you for any help.


Simon

 

[Peter Atherton]

Hello Simon

Press ALT F11, Insert Module and paste the following code.

Sub CondFormat()
Dim n As Integer
Cells(2, 1).Select
n = ActiveCell.CurrentRegion.Rows.Count

For i = 2 To n
Cells(i, 1).Select
If ActiveCell.Offset(0, 1) = ActiveCell.Offset(0, 2) Then
ActiveCell.Interior.ColorIndex = xlNone
ElseIf ActiveCell.Offset(0, 1).Value > ActiveCell.Offset
(0, 2) Then
ActiveCell.Interior.ColorIndex = 3
Else: ActiveCell.Interior.ColorIndex = 41
End If
Next i

End Sub

This produces the results that you showed in the example,
change the index values if you want to swap them.

It also leaves the formatting blank if column 3 and column
2 values are the same. Delete this line if you do not want
this and change the ElseIf to If

Run the Macro from the Tools, Macro, Run menu in Excel.

Regards
Peter