How do I show only the cell in a row with the greatest value?

[Guest]

In Microsoft Excel I have a row with a different value in each cell and I am
looking to show only the cell that has the greatest value in that row. I
would like for all the other cells to be hidden in that row. I would also
like the program to do this automatically. Any ideas how to complete this?

Thanks!

 

[Guest]

Press Alt+F11 to open the VBE, click INSERT on the menu & select MODULE.

Paste the following sub into the module, modifying the row value of "A1:Z1"
to the row relevant to your worksheet. The module resides in between the
asterisks and assumes that it will be run from the worksheet you want to
highlight the max value in.

***********************************************************
Sub HighlightMax()

Dim r As Range
Dim varVal As Variant
Dim dblMaxVal As Double
Dim intColOffset As Integer

'Set the row range
Set r = Range("A1:Z1")
'Get the maximum value

dblMaxVal = Application.WorksheetFunction.Max(r)
'Capture first value in the range
varVal = Range("A1").Value

'Loop until the first blank cell is encountered
Do Until varVal = ""
If IsNumeric(varVal) Then
If CDbl(varVal) <> dblMaxVal Then
Range("A1").Offset(, intColOffset). _
NumberFormat = ";;;"
End If
End If
'increment the column offset value by 1 & get next value
intColOffset = intColOffset + 1
varVal = Range("A1").Offset(, intColOffset).Value
Loop

Set r = Nothing

End Sub
************************************************************

 

[Don Guillett]

This should do it but now you need a macro to unhide for changes. Why not
use conditional formatting instead?
Sub findmaxandshow()
With ActiveSheet
mv = .Rows(3).Find(Application.Max(.Rows(3))).Column
'MsgBox mv
.Columns.Hidden = True
.Columns(mv).Hidden = False
Application.Goto .Cells(3, mv)
End With
End Sub

 

[Don Guillett]

For CF. Highlight the row header (ie 3) >format>conditional
formatting>formula is
=a3=max(3:3)