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:

    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, _
            MatchCase:=False).Activate
            ActiveCell.Font.ColorIndex = 3
    End Sub


    Why this is not working?

  • Re: find function is not working


    Hi Joseph,


    Try this:


    [vba]
    Sub find()
    Dim maxnum As Double
    maxnum = Application.WorksheetFunction.max(Sheet1.Range("data"))
    Sheet1.Range("Data").find(what:=maxnum).Font.ColorIndex=3
    End Sub
    [/vba]


    Hope this helps.

  • Re: find function is not working


    Hello


    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:

    Code
    Sub findex()
    Dim m As Integer, wccpk As Double, cpkrange As Range
    Set cpkrange = Range("n12:ac12")
    wccpk = WorksheetFunction.Min(cpkrange)
    cpkrange.find(what:=wccpk).Activate
    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!