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

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

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

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

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