EXCEL 2010 find max value cell and change its color

Question

I am working on VBA EXCEL 2010.

I need to find the max value in a column and highlight its cell with a color.

Sub findMax_1()
    Dim c As Range
    Dim max As Double
    Dim maxCell As String
     max = 0
     For Each c In Selection
        If c.Value > max Then
           max = c.Value
           maxCell = c.Address
        End If
     Next c
     ActiveSheet.Range("A10") = max
     ActiveSheet.Range(maxCell).Color = vbBlue
 End Sub

It does not work. Runtime error 438.

Any help would be appreciated.

Answer 1

As simco mentioned in the comments you would need to change the following line of code:

ActiveSheet.Range(maxCell).Color = vbBlue

To

ActiveSheet.Range(maxCell).Interior.Color = vbBlue

The problem with your current code is that if you have nothing selected you would end up with a 1004 error. One way of overcoming this is as simco mentioned to check if you have any cells selected. The method below is the method I preferr. Lets say you have your data in Column A:

Use the code below:

Sub findMax_1()
    Dim c As Range
    Dim flag As Boolean
    Dim i As Integer

    Dim max As Double
    Dim maxCell As String
    flag = True
    i = 1
    max = 0
    While flag = True
        If Cells(i, 1) <> "" Then

            If Cells(i, 1) > max Then
               max = Cells(i, 1)
               maxCell = Range(Cells(i, 1), Cells(i, 1)).Address

            End If
              i = i + 1
        Else
            flag = False
        End If

    Wend
     ActiveSheet.Range("A10") = max
     ActiveSheet.Range(maxCell).Interior.Color = vbBlue
 End Sub

Result:

Also you could look at this article on my blog for more information Excel VBA Formatting Cells and Range

---

Also as simco mentioned you could use conditional formatting, Select the column with the data:

From the Home Ribbon Select Conditional Formatting>>Top/Bottom Rules >> Top 10 Items ...>>

Select "1" From the left text box and choose your color from the drop down list on the right: