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!

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
************************************************************
--
Kevin Backmann


"Steph" wrote:

> 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!

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
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Steph" <(E-Mail Removed)> wrote in message
news0CF1061-533C-4467-A5AE-(E-Mail Removed)...
> 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!

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Steph" <(E-Mail Removed)> wrote in message
news0CF1061-533C-4467-A5AE-(E-Mail Removed)...
> 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!