Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]() Hello Anyone: Best wishes. I have an excel report sheet that contains 3 sets of data in tabular form. For each set, I will find maximum value and will highlight the cell. This maximum value is in columns b,d,f,h and j. I am selecting each set of data as sepatre range and finding and highlighting my cell. ----------code range1.select with selection set fndcell=cells.find(what:=val,after:=activecell,loo kin:=xlvalues,lookat:=xlpart(orxlwhole),searchorde r:=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 from range1(for set1 data) and highlighting it. The range2 selection is fine but .find() is going outside the selection and trying to see the data there. what is the wrong in my code.Is the problem in the find() cluase? Thanks in advance -- jesmin ------------------------------------------------------------------------ jesmin's Profile: http://www.excelforum.com/member.php...o&userid=29540 View this thread: http://www.excelforum.com/showthread...hreadid=496471 |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 jesmin wrote: Hello Anyone: Best wishes. I have an excel report sheet that contains 3 sets of data in tabular form. For each set, I will find maximum value and will highlight the cell. This maximum value is in columns b,d,f,h and j. I am selecting each set of data as sepatre range and finding and highlighting my cell. ----------code range1.select with selection set fndcell=cells.find(what:=val,after:=activecell,loo kin:=xlvalues,lookat:=xlpart(orxlwhole),searchorde r:=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 from range1(for set1 data) and highlighting it. The range2 selection is fine but .find() is going outside the selection and trying to see the data there. what is the wrong in my code.Is the problem in the find() cluase? Thanks in advance -- jesmin ------------------------------------------------------------------------ jesmin's Profile: http://www.excelforum.com/member.php...o&userid=29540 View this thread: http://www.excelforum.com/showthread...hreadid=496471 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Complicated If Then / V Lookup / Match Statement... | Excel Worksheet Functions | |||
Need 2 add second then third code with first code in the Tab View | Excel Worksheet Functions | |||
Conform a total to a list of results? | Excel Discussion (Misc queries) | |||
Macro for changing text to Proper Case | Excel Worksheet Functions | |||
Often-Used Code not working in a new Workbook | Excel Discussion (Misc queries) |