Why code for find() is not working

[jesmin]

Hello Anyone:
Best wishes. I have an excel report sheet that contains 3 sets of dat
in tabular form. For each set, I will find maximum value and wil
highlight the cell. This maximum value is in columns b,d,f,h and j.
am selecting each set of data as sepatre range and finding an
highlighting my cell.
----------code
range1.select
with selection
se
fndcell=cells.find(what:=val,after:=activecell,lookin:=xlvalues,lookat:=xlpart(orxlwhole),searchorder:=xlbycolumns,searchdirection:=xlnext,matchcase:=true).
end with
problem:
I have a max value in both 1st and 2nd set.(say 500). In my 2nd loop
though I am selecting 2nd range, its finding the 500 max value fro
range1(for set1 data) and highlighting it. The range2 selection is fin
but .find() is going outside the selection and trying to see the dat
there.
what is the wrong in my code.Is the problem in the find() cluase?
Thanks in advanc

 

[Dave Peterson]

It's probably best to copy the code directly from the VBE and paste into your
message. Lots of stuff can get lost if you just type in the compose window (of
excelforum).

You've got a couple of typos in that line and it's difficult to guess whether
the typo is in your code or just in your post.

set fndcell = .cells... (notice the leading dot)

the lookat:= is one of those--not what you posted.

But this kind of code worked ok for me:

Option Explicit
Sub HighlightMax()

Dim myRng As Range
Dim wks As Worksheet
Dim myMax As Double
Dim myCol As Range
Dim FoundCell As Range
Set wks = ActiveSheet

With wks
Set myRng = .Range("b1,d1,f1,h1,j1").EntireColumn
For Each myCol In myRng.Columns
With myCol
myMax = Application.Max(.Cells)
Set FoundCell = .Find(what:=myMax, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, lookat:=xlWhole, _
searchorder:=xlByRows, _
searchdirection:=xlNext)
If FoundCell Is Nothing Then
'do nothing (all text???)
Else
FoundCell.Interior.ColorIndex = 6
End If
End With
Next myCol
End With

End Sub