xiahuagreen
New Member
- Joined
- Oct 19, 2019
- Messages
- 5
Hello
below is my code, it works only 1/3 of the upper screen as expected, error msg comes out anywhere else. please help to identify the root cause. Thanks so much.
Sub FindMax()
'
Dim zoomCell As Range, zoomRng As Range, maxCell As Variant
ActiveSheet.Cells.Interior.Color = xlNone 'Clear out old highlight colors first
If Selection.Cells.Count = 1 Then
MsgBox "You must select a range before pressing button - Try again"
End
End If
Set zoomRng = Application.Selection
Selection.Name = "zoomArea"
For Each zoomCell In Range(zoomRng.Address)
zoomCell.Cells.Interior.ColorIndex = 6 'Yellow highlight selected cells.
Next zoomCell
maxCell = WorksheetFunction.Max(Range(zoomRng.Address)) 'obtain address for each cell
With ActiveSheet.Range(zoomRng.Address)
Set c = .Find(maxCell, LookIn:=xlValues) 'find the max value cell
If Not c Is Nothing Then
maxaddress = c.Address 'Find max value in select cells.
End If
End With
ActiveWindow.Zoom = True
Range(maxaddress).Cells.Interior.ColorIndex = 22 'Red highlight for Max value. <<<<<<<<<<<<< This line is in error when debug
MsgBox "Max value = " & maxCell & " at cell " & maxaddress
ActiveWindow.Zoom = 70
maxaddress = Clear
maxCell = Clear
zoomCell = Clear
End Sub
below is my code, it works only 1/3 of the upper screen as expected, error msg comes out anywhere else. please help to identify the root cause. Thanks so much.
Sub FindMax()
'
Dim zoomCell As Range, zoomRng As Range, maxCell As Variant
ActiveSheet.Cells.Interior.Color = xlNone 'Clear out old highlight colors first
If Selection.Cells.Count = 1 Then
MsgBox "You must select a range before pressing button - Try again"
End
End If
Set zoomRng = Application.Selection
Selection.Name = "zoomArea"
For Each zoomCell In Range(zoomRng.Address)
zoomCell.Cells.Interior.ColorIndex = 6 'Yellow highlight selected cells.
Next zoomCell
maxCell = WorksheetFunction.Max(Range(zoomRng.Address)) 'obtain address for each cell
With ActiveSheet.Range(zoomRng.Address)
Set c = .Find(maxCell, LookIn:=xlValues) 'find the max value cell
If Not c Is Nothing Then
maxaddress = c.Address 'Find max value in select cells.
End If
End With
ActiveWindow.Zoom = True
Range(maxaddress).Cells.Interior.ColorIndex = 22 'Red highlight for Max value. <<<<<<<<<<<<< This line is in error when debug
MsgBox "Max value = " & maxCell & " at cell " & maxaddress
ActiveWindow.Zoom = 70
maxaddress = Clear
maxCell = Clear
zoomCell = Clear
End Sub