Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding a cell with 2 conditions
Hi, Trying to write a macro which finds the text "Job No" and if it has colorindex = 37 then assigns the column number of "Job No" to the variable m Below isnt quite working: Sub fi() taz = Cells.Find(What:="Job No", After:=ActiveCell, LookIn:=xlFormulas LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext MatchCase:= _ False, SearchFormat:=False).Activate If taz.Interior.ColorIndex = 37 Then m = taz.Column End If End Su -- T De Villier ----------------------------------------------------------------------- T De Villiers's Profile: http://www.excelforum.com/member.php...fo&userid=2647 View this thread: http://www.excelforum.com/showthread.php?threadid=56599 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding a cell with 2 conditions
And by "isnt quite working" you mean what exactly?
Die_Another_Day T De Villiers wrote: Hi, Trying to write a macro which finds the text "Job No" and if it has colorindex = 37 then assigns the column number of "Job No" to the variable m Below isnt quite working: Sub fi() taz = Cells.Find(What:="Job No", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Activate If taz.Interior.ColorIndex = 37 Then m = taz.Column End If End Sub -- T De Villiers ------------------------------------------------------------------------ T De Villiers's Profile: http://www.excelforum.com/member.php...o&userid=26479 View this thread: http://www.excelforum.com/showthread...hreadid=565990 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding a cell with 2 conditions
Try this, its a little simpler:
Dim taz As Range Set taz = Cells.Find(What:="Job No") If taz Is Nothing Then 'do nothing Else If taz.Interior.ColorIndex = 37 Then m = taz.Column End If End If -- Les Torchia-Wells "T De Villiers" wrote: Hi, Trying to write a macro which finds the text "Job No" and if it has colorindex = 37 then assigns the column number of "Job No" to the variable m Below isnt quite working: Sub fi() taz = Cells.Find(What:="Job No", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Activate If taz.Interior.ColorIndex = 37 Then m = taz.Column End If End Sub -- T De Villiers ------------------------------------------------------------------------ T De Villiers's Profile: http://www.excelforum.com/member.php...o&userid=26479 View this thread: http://www.excelforum.com/showthread...hreadid=565990 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding a cell with 2 conditions
Sub fi()
Dim taz as Range set taz = Cells.Find(What:="Job No", _ After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False) if taz is nothing then msgbox "Job No not found" exit sub else If taz.Interior.ColorIndex = 37 Then m = taz.Column End If End if End Sub -- Regards, Tom Ogilvy "T De Villiers" wrote: Hi, Trying to write a macro which finds the text "Job No" and if it has colorindex = 37 then assigns the column number of "Job No" to the variable m Below isnt quite working: Sub fi() taz = Cells.Find(What:="Job No", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Activate If taz.Interior.ColorIndex = 37 Then m = taz.Column End If End Sub -- T De Villiers ------------------------------------------------------------------------ T De Villiers's Profile: http://www.excelforum.com/member.php...o&userid=26479 View this thread: http://www.excelforum.com/showthread...hreadid=565990 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding a cell with 2 conditions
Just a thought for the OP:
since FIND has persistent properties that can cause inconsistent performance, it is usually best to specify all properties. I doubt Les was suggesting that you should not do this but only provided a simplified example for expediency. -- Regards, Tom Ogilvy "Les" wrote: Try this, its a little simpler: Dim taz As Range Set taz = Cells.Find(What:="Job No") If taz Is Nothing Then 'do nothing Else If taz.Interior.ColorIndex = 37 Then m = taz.Column End If End If -- Les Torchia-Wells "T De Villiers" wrote: Hi, Trying to write a macro which finds the text "Job No" and if it has colorindex = 37 then assigns the column number of "Job No" to the variable m Below isnt quite working: Sub fi() taz = Cells.Find(What:="Job No", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Activate If taz.Interior.ColorIndex = 37 Then m = taz.Column End If End Sub -- T De Villiers ------------------------------------------------------------------------ T De Villiers's Profile: http://www.excelforum.com/member.php...o&userid=26479 View this thread: http://www.excelforum.com/showthread...hreadid=565990 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
if conditions in 2 cell correct then display third cell | New Users to Excel | |||
Finding the average using conditions in a logic function - problem | Excel Discussion (Misc queries) | |||
Countif Conditions - Use of conditions that vary by cell value | Excel Discussion (Misc queries) | |||
How do I make a cell refrence another cell based on conditions? | Excel Worksheet Functions | |||
Conditional formatting Based on cell A text with conditions in Cell B | Excel Discussion (Misc queries) |