D
Dennis
I have a spreadsheet of 30 columns and 5000 rows. The first row is a label row.
I applied an autofilter (like greater than 20) to one of the columns and am
showing the visible rows that satisfy the autofiltered column. I wish to color
the maximum and minimum values in each of the visible autofiltered columns
The code below takes a very loong time to complete AND it somehow increases the
rows in the spreadsheet to the maximum 65000.
What am I doing wrong?
Thanks for any help.
my code snippet is:
SS = ActiveSheet.Name
Set curwk = Sheets(SS)
With curwk
'Reset All Interior cells to standard color index
.Cells.Select
Selection.Interior.ColorIndex = xlNone
'Cells.Deselect
.Range("A2").Select
For ii = 2 To LastCol
Set rng = .Columns(ii).SpecialCells(xlCellTypeVisible)
MaxVal = Application.Max(rng)
MinVal = Application.Min(rng)
For Each myCell In rng
CellVal = myCell.Value
If IsNumeric(CellVal) = True Then
If CellVal = MaxVal Then myCell.Interior.ColorIndex = 4
If CellVal = MinVal Then myCell.Interior.ColorIndex = 6
End If
Next
Next ii
End With
I applied an autofilter (like greater than 20) to one of the columns and am
showing the visible rows that satisfy the autofiltered column. I wish to color
the maximum and minimum values in each of the visible autofiltered columns
The code below takes a very loong time to complete AND it somehow increases the
rows in the spreadsheet to the maximum 65000.
What am I doing wrong?
Thanks for any help.
my code snippet is:
SS = ActiveSheet.Name
Set curwk = Sheets(SS)
With curwk
'Reset All Interior cells to standard color index
.Cells.Select
Selection.Interior.ColorIndex = xlNone
'Cells.Deselect
.Range("A2").Select
For ii = 2 To LastCol
Set rng = .Columns(ii).SpecialCells(xlCellTypeVisible)
MaxVal = Application.Max(rng)
MinVal = Application.Min(rng)
For Each myCell In rng
CellVal = myCell.Value
If IsNumeric(CellVal) = True Then
If CellVal = MaxVal Then myCell.Interior.ColorIndex = 4
If CellVal = MinVal Then myCell.Interior.ColorIndex = 6
End If
Next
Next ii
End With