Imagine you have data in 10 adjacent cells starting with A1. Highlight
the 10 cells so that A1 is the activecell and click Format |
Conditional Formatting. In the panel that pops up, select Formula Is
rather than Cell Value Is in the first box, and put this formula in
the second box:

=A1=LARGE($A$1:$J$10,1)

Then click on the Format button, select the Patterns tab (for
background colour) and click on Red. Press OK to get back to the CF
dialogue box, then click Add and set up your second condition similar
to the first but with this formula:

=A1=LARGE($A$1:$J$10,2)

and with Green as the background colour. This time press OK twice, and
then you should find one cell is red (for the largest value) and
another cell is green (next largest). Change values in other cells to
check out it is working how you wanted.

Hope this helps.

Pete

You need to use VBA to store all the old values so that it can be
determined which cell used to be the maximum before the user made a
change.

One way is to use a SelectionChange Event procedure to store the row's
values in a Public variant array and the maximum of those values in a
Public variable. Then use a WorksheetChange Event procedure to apply
the green fill to the cell that used to be the maximum before the
change was made. If your sheet is set up to Move on Enter, then
straight after the change is made and the WorksheetChange Event
procedure has run, the SelectionChange Event procedure is run again to
store the new values ready for the next time you change a cell's value
in that range.

Maybe something like this, which should be pasted into the worksheet's
code module...

Public sgOldMax As Single, vaOld
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:C1")) Is Nothing Then
Dim I As Long
Range("A1:C1").Interior.ColorIndex = xlNone
For I = 1 To 3
If vaOld(1, I) = sgOldMax Then
Cells(1, I).Interior.ColorIndex = 4
End If
Next I
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
sgOldMax = WorksheetFunction.Max(Range("A1:C1"))
vaOld = Range("A1:C1")
End Sub

You would still use Conditional Formatting to get the current maximum
with the red fill, and since Conditional formatting has precedence
over the code, when the changed cell is both the new and old maximum
you will only see the red fill.

Ken Johnson

Wed, 13 Feb 2008 20:09:55 -0000 from Tracy <tracydavidson5000
@yahoo.co.uk>:

You mean e.g., not i.e.  Why do people persist in using abbreviations
when they don't know what they mean?

Okay, 36 columns is A through AJ.

Highlight A1 and select Format | Conditional Formatting.

Change from Cell Value to Formula.

Enter =A1=max($A1:$AJ1) and set your format as desired.  Click OK.

Edit | Copy, then highlight B1:AJ1 and Edit | Paste Special |
Formats.