find function is not working

  • In the attached worksheet, I am using the find function to find the maximum of a range and change its font.colorindex.

    This is the code:

    Sub find()
    Dim max As Double
    max = WorksheetFunction.max(Range("data"))
     Range("data").find(What:=Format(max), LookIn:=xlValues, _
             LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            ActiveCell.Font.ColorIndex = 3
    End Sub

    Why this is not working?

  • Re: find function is not working

    Hi Joseph,

    Try this:

    Sub find()
    Dim maxnum As Double
    maxnum = Application.WorksheetFunction.max(Sheet1.Range("data"))
    End Sub

    Hope this helps.

  • Re: find function is not working


    It is working if I cahnge the wdith of the column so that the data is displayed with all the decimal places. But if the width is such that only two decimal places are displyed, not working.

    Please have a look on the following code:

    Sub findex()
    Dim m As Integer, wccpk As Double, cpkrange As Range
    Set cpkrange = Range("n12:ac12")
    wccpk = WorksheetFunction.Min(cpkrange)
    End Sub

    This is also not working....
    Not able to understand this...

    Looking forward for your help.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!