cell formating

[Tracy]

Hi I have a spreadsheet that has varying amounts in a row of cells. How do I
format the cells so that the cell with the highest value will have a red
background and if I change a value in a cell to a higher value that cell
will have a red background and the previous cell with the highest value will
change to a green background?

i.e A1 = 5 B1 = 7 C1 = 4 etc

so B1 has a red background. If I then change B1 = 3 then B1 has a green
background and A1 automatically has a red background. It is extremelly
unlikely that any of the cells will have equal values as the values are
currency and in my spreadsheet at present with 36 columns no two cells have
the same value.

Thanks, Tracy

 

[Pete_UK]

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

 

[Ken Johnson]

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

 

[Stan Brown]

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

Hi I have a spreadsheet that has varying amounts in a row of cells. How do I
format the cells so that the cell with the highest value will have a red
background and if I change a value in a cell to a higher value that cell
will have a red background and the previous cell with the highest value will
change to a green background?

i.e A1 = 5 B1 = 7 C1 = 4 etc

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

currency and in my spreadsheet at present with 36 columns no two cells have

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.

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com
A: Maybe because some people are too annoyed by top posting.
Q: Why do I not get an answer to my question(s)?
A: Because it messes up the order in which people normally read text.
Q: Why is top-posting such a bad thing?

 

[Ken Johnson]

For 36 columns (A to AJ)...

Public sgOldMax As Single, vaOld
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:AJ1")) Is Nothing Then
Dim I As Long
Range("A1:AJ1").Interior.ColorIndex = xlNone
For I = 1 To 36
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:AJ1"))
vaOld = Range("A1:AJ1")
End Sub

Ken Johnson