ScottyBee
08-04-2020, 12:09 PM
Hello,
The below code works just fine but is including data in column B which I do not want
Sub ColorMaximumValue()
Dim rng As Range, cell As Range, Maximum As Double
Cells.Interior.ColorIndex = 0
Set rng = Range("A1").CurrentRegion
'Set rng = Range("A1", Range("A1").End(xlDown)).Select
Maximum = Application.WorksheetFunction.Max(rng)
For Each cell In rng
If cell.Value = Maximum Then
cell.Interior.ColorIndex = 22
End If
Next cell
End Sub
26947
I adjusted the code to be:
Sub ColorMaximumValue()
Dim rng As Range, cell As Range, Maximum As Double
Cells.Interior.ColorIndex = 0
'Set rng = Range("A1").CurrentRegion
Set rng = Range("A1", Range("A1").End(xlDown)).Select
Maximum = Application.WorksheetFunction.Max(rng)
For Each cell In rng
If cell.Value = Maximum Then
cell.Interior.ColorIndex = 22
End If
Next cell
End Sub
I get "Object Required" Error. I am not sure why as I tested Range("A1", Range("A1").End(xlDown)).Select in the immediate window and it worked just fine. Any ideas? Thanks
The below code works just fine but is including data in column B which I do not want
Sub ColorMaximumValue()
Dim rng As Range, cell As Range, Maximum As Double
Cells.Interior.ColorIndex = 0
Set rng = Range("A1").CurrentRegion
'Set rng = Range("A1", Range("A1").End(xlDown)).Select
Maximum = Application.WorksheetFunction.Max(rng)
For Each cell In rng
If cell.Value = Maximum Then
cell.Interior.ColorIndex = 22
End If
Next cell
End Sub
26947
I adjusted the code to be:
Sub ColorMaximumValue()
Dim rng As Range, cell As Range, Maximum As Double
Cells.Interior.ColorIndex = 0
'Set rng = Range("A1").CurrentRegion
Set rng = Range("A1", Range("A1").End(xlDown)).Select
Maximum = Application.WorksheetFunction.Max(rng)
For Each cell In rng
If cell.Value = Maximum Then
cell.Interior.ColorIndex = 22
End If
Next cell
End Sub
I get "Object Required" Error. I am not sure why as I tested Range("A1", Range("A1").End(xlDown)).Select in the immediate window and it worked just fine. Any ideas? Thanks