dee
 

hi
I want to find some data in excel sheet and want to change the selecte
data into a different color how to do it with a macro

Thanx in advance
de

--
dee241
-----------------------------------------------------------------------
dee2417's Profile: http://www.excelforum.com/member.php...fo&userid=2458
View this thread: http://www.excelforum.com/showthread.php?threadid=38179

dee
 

Here is the help example for the FIND method:

This example finds all cells in the range A1:A500 that contain the value 2
and makes those cells gray.

With Worksheets(1).Range("a1:a500")
Set c = .Find(2, lookin:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Interior.Pattern = xlPatternGray50
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

--
Regards,
Tom Ogilvy


"dee2417" wrote in
message ...

hi
I want to find some data in excel sheet and want to change the selected
data into a different color how to do it with a macro

Thanx in advance
dee


--
dee2417
------------------------------------------------------------------------
dee2417's Profile:
http://www.excelforum.com/member.php...o&userid=24587
View this thread: http://www.excelforum.com/showthread...hreadid=381794

dee
 

pls don't multipost

--
Don Guillett
SalesAid Software

"dee2417" wrote in
message ...

hi
I want to find some data in excel sheet and want to change the selected
data into a different color how to do it with a macro

Thanx in advance
dee


--
dee2417
------------------------------------------------------------------------
dee2417's Profile:
http://www.excelforum.com/member.php...o&userid=24587
View this thread: http://www.excelforum.com/showthread...hreadid=381794

dee
 

Tom.
This code finds and highlights all cells that include "2" anywhere within
the cell, like "1234". But what if you wanted only cells with the value
"2".
Tks,


"Tom Ogilvy" wrote in message
...
Here is the help example for the FIND method:

This example finds all cells in the range A1:A500 that contain the value 2
and makes those cells gray.

With Worksheets(1).Range("a1:a500")
Set c = .Find(2, lookin:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Interior.Pattern = xlPatternGray50
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

--
Regards,
Tom Ogilvy


"dee2417" wrote in
message ...

hi
I want to find some data in excel sheet and want to change the selected
data into a different color how to do it with a macro

Thanx in advance
dee


--
dee2417
------------------------------------------------------------------------
dee2417's Profile:
http://www.excelforum.com/member.php...o&userid=24587
View this thread:
http://www.excelforum.com/showthread...hreadid=381794

dee
 

..Find is one of those methods that remember the settings the last time it was
used--either via code or via the userinterface.

If you look at the help for .find, you'll see all the options that can be
specified. (This is from xl2003.)


expression.Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection,
MatchCase, MatchByte, SearchFormat)

It may be better to specify all the options that are available than to rely on
having the settings the way you want.

(Tom copied the example from the help -- which is less than robust.)

Jim May wrote:

Tom.
This code finds and highlights all cells that include "2" anywhere within
the cell, like "1234". But what if you wanted only cells with the value
"2".
Tks,

"Tom Ogilvy" wrote in message
...
Here is the help example for the FIND method:

This example finds all cells in the range A1:A500 that contain the value 2
and makes those cells gray.

With Worksheets(1).Range("a1:a500")
Set c = .Find(2, lookin:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Interior.Pattern = xlPatternGray50
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

--
Regards,
Tom Ogilvy


"dee2417" wrote in
message ...

hi
I want to find some data in excel sheet and want to change the selected
data into a different color how to do it with a macro

Thanx in advance
dee


--
dee2417
------------------------------------------------------------------------
dee2417's Profile:
http://www.excelforum.com/member.php...o&userid=24587
View this thread:
http://www.excelforum.com/showthread...hreadid=381794




--

Dave Peterson

dee
 

Just to add:
It is robust enough, but less than comprehensive; however it is always
readily available as a consult for a generalized algorithm. It does have
some warts, for example the loop termination conditions, not setting args.
As Dave said, it is always best to include the settings you want FIND to
use.

--
Regards,
Tom Ogilvy


"Dave Peterson" wrote in message
...
.Find is one of those methods that remember the settings the last time it
was
used--either via code or via the userinterface.

If you look at the help for .find, you'll see all the options that can be
specified. (This is from xl2003.)


expression.Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection,
MatchCase, MatchByte, SearchFormat)

It may be better to specify all the options that are available than to
rely on
having the settings the way you want.

(Tom copied the example from the help -- which is less than robust.)

Jim May wrote:

Tom.
This code finds and highlights all cells that include "2" anywhere
within
the cell, like "1234". But what if you wanted only cells with the value
"2".
Tks,

"Tom Ogilvy" wrote in message
...
Here is the help example for the FIND method:

This example finds all cells in the range A1:A500 that contain the
value 2
and makes those cells gray.

With Worksheets(1).Range("a1:a500")
Set c = .Find(2, lookin:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Interior.Pattern = xlPatternGray50
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

--
Regards,
Tom Ogilvy


"dee2417" wrote
in
message ...

hi
I want to find some data in excel sheet and want to change the
selected
data into a different color how to do it with a macro

Thanx in advance
dee


--
dee2417

------------------------------------------------------------------------
dee2417's Profile:
http://www.excelforum.com/member.php...o&userid=24587
View this thread:
http://www.excelforum.com/showthread...hreadid=381794




--

Dave Peterson

dee
 

Also, notices that if my range is formatted numbers (comma, 2) the Macro
yields nothing;
If I change formatting the General (Control+Shift+~) macro paints my four
2's. Find method must be sensitive to formatting,,, hummmm

My Code:
Sub Foo()
With Worksheets(1).Range("a1:a15")
Set c = .Find(2, Lookat:=xlWhole, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Interior.ColorIndex = 6
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
End Sub




"Tom Ogilvy" wrote in message
...
Just to add:
It is robust enough, but less than comprehensive; however it is always
readily available as a consult for a generalized algorithm. It does have
some warts, for example the loop termination conditions, not setting args.
As Dave said, it is always best to include the settings you want FIND to
use.

--
Regards,
Tom Ogilvy


"Dave Peterson" wrote in message
...
.Find is one of those methods that remember the settings the last time
it
was
used--either via code or via the userinterface.

If you look at the help for .find, you'll see all the options that can
be
specified. (This is from xl2003.)


expression.Find(What, After, LookIn, LookAt, SearchOrder,
SearchDirection,
MatchCase, MatchByte, SearchFormat)

It may be better to specify all the options that are available than to
rely on
having the settings the way you want.

(Tom copied the example from the help -- which is less than robust.)

Jim May wrote:

Tom.
This code finds and highlights all cells that include "2" anywhere
within
the cell, like "1234". But what if you wanted only cells with the
value
"2".
Tks,

"Tom Ogilvy" wrote in message
...
Here is the help example for the FIND method:

This example finds all cells in the range A1:A500 that contain the
value 2
and makes those cells gray.

With Worksheets(1).Range("a1:a500")
Set c = .Find(2, lookin:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Interior.Pattern = xlPatternGray50
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

--
Regards,
Tom Ogilvy


"dee2417"
wrote
in
message ...

hi
I want to find some data in excel sheet and want to change the
selected
data into a different color how to do it with a macro

Thanx in advance
dee


--
dee2417

------------------------------------------------------------------------
dee2417's Profile:
http://www.excelforum.com/member.php...o&userid=24587
View this thread:
http://www.excelforum.com/showthread...hreadid=381794




--

Dave Peterson

dee
 

May not be as fast but try this.

Sub find2()
For Each c In Sheets("sheet11").Range("a1:a15")
If c = 2 Then c.Interior.ColorIndex = 6
Next c
End Sub

--
Don Guillett
SalesAid Software

"Jim May" wrote in message
news:pDbve.67533$Fv.13324@lakeread01...
Also, notices that if my range is formatted numbers (comma, 2) the Macro
yields nothing;
If I change formatting the General (Control+Shift+~) macro paints my four
2's. Find method must be sensitive to formatting,,, hummmm

My Code:
Sub Foo()
With Worksheets(1).Range("a1:a15")
Set c = .Find(2, Lookat:=xlWhole, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Interior.ColorIndex = 6
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
End Sub




"Tom Ogilvy" wrote in message
...
Just to add:
It is robust enough, but less than comprehensive; however it is always
readily available as a consult for a generalized algorithm. It does have
some warts, for example the loop termination conditions, not setting
args.
As Dave said, it is always best to include the settings you want FIND to
use.

--
Regards,
Tom Ogilvy


"Dave Peterson" wrote in message
...
.Find is one of those methods that remember the settings the last time
it
was
used--either via code or via the userinterface.

If you look at the help for .find, you'll see all the options that can
be
specified. (This is from xl2003.)


expression.Find(What, After, LookIn, LookAt, SearchOrder,
SearchDirection,
MatchCase, MatchByte, SearchFormat)

It may be better to specify all the options that are available than to
rely on
having the settings the way you want.

(Tom copied the example from the help -- which is less than robust.)

Jim May wrote:

Tom.
This code finds and highlights all cells that include "2" anywhere
within
the cell, like "1234". But what if you wanted only cells with the
value
"2".
Tks,

"Tom Ogilvy" wrote in message
...
Here is the help example for the FIND method:

This example finds all cells in the range A1:A500 that contain the
value 2
and makes those cells gray.

With Worksheets(1).Range("a1:a500")
Set c = .Find(2, lookin:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Interior.Pattern = xlPatternGray50
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

--
Regards,
Tom Ogilvy


"dee2417"
wrote
in
message
...

hi
I want to find some data in excel sheet and want to change the
selected
data into a different color how to do it with a macro

Thanx in advance
dee


--
dee2417


------------------------------------------------------------------------
dee2417's Profile:
http://www.excelforum.com/member.php...o&userid=24587
View this thread:
http://www.excelforum.com/showthread...hreadid=381794




--

Dave Peterson

dee
 

OR
Sub Fooo()
With Sheet11.Range("a1:a15")
.NumberFormat = "General"
Set c = .Find(2, lookat:=xlWhole)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Interior.ColorIndex = 6
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
.Style = "Comma"
End With
End Sub

--
Don Guillett
SalesAid Software

"Jim May" wrote in message
news:pDbve.67533$Fv.13324@lakeread01...
Also, notices that if my range is formatted numbers (comma, 2) the Macro
yields nothing;
If I change formatting the General (Control+Shift+~) macro paints my four
2's. Find method must be sensitive to formatting,,, hummmm

My Code:
Sub Foo()
With Worksheets(1).Range("a1:a15")
Set c = .Find(2, Lookat:=xlWhole, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Interior.ColorIndex = 6
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
End Sub




"Tom Ogilvy" wrote in message
...
Just to add:
It is robust enough, but less than comprehensive; however it is always
readily available as a consult for a generalized algorithm. It does have
some warts, for example the loop termination conditions, not setting
args.
As Dave said, it is always best to include the settings you want FIND to
use.

--
Regards,
Tom Ogilvy


"Dave Peterson" wrote in message
...
.Find is one of those methods that remember the settings the last time
it
was
used--either via code or via the userinterface.

If you look at the help for .find, you'll see all the options that can
be
specified. (This is from xl2003.)


expression.Find(What, After, LookIn, LookAt, SearchOrder,
SearchDirection,
MatchCase, MatchByte, SearchFormat)

It may be better to specify all the options that are available than to
rely on
having the settings the way you want.

(Tom copied the example from the help -- which is less than robust.)

Jim May wrote:

Tom.
This code finds and highlights all cells that include "2" anywhere
within
the cell, like "1234". But what if you wanted only cells with the
value
"2".
Tks,

"Tom Ogilvy" wrote in message
...
Here is the help example for the FIND method:

This example finds all cells in the range A1:A500 that contain the
value 2
and makes those cells gray.

With Worksheets(1).Range("a1:a500")
Set c = .Find(2, lookin:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Interior.Pattern = xlPatternGray50
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With

--
Regards,
Tom Ogilvy


"dee2417"
wrote
in
message
...

hi
I want to find some data in excel sheet and want to change the
selected
data into a different color how to do it with a macro

Thanx in advance
dee


--
dee2417


------------------------------------------------------------------------
dee2417's Profile:
http://www.excelforum.com/member.php...o&userid=24587
View this thread:
http://www.excelforum.com/showthread...hreadid=381794




--

Dave Peterson

dee
 

tks Don;

"Don Guillett" wrote in message
...
OR
Sub Fooo()
With Sheet11.Range("a1:a15")
.NumberFormat = "General"
Set c = .Find(2, lookat:=xlWhole)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Interior.ColorIndex = 6
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
.Style = "Comma"
End With
End Sub

--
Don Guillett
SalesAid Software

"Jim May" wrote in message
news:pDbve.67533$Fv.13324@lakeread01...
Also, notices that if my range is formatted numbers (comma, 2) the Macro
yields nothing;
If I change formatting the General (Control+Shift+~) macro paints my
four
2's. Find method must be sensitive to formatting,,, hummmm

My Code:
Sub Foo()
With Worksheets(1).Range("a1:a15")
Set c = .Find(2, Lookat:=xlWhole, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Interior.ColorIndex = 6
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
End Sub




"Tom Ogilvy" wrote in message
...
Just to add:
It is robust enough, but less than comprehensive; however it is always
readily available as a consult for a generalized algorithm. It does
have
some warts, for example the loop termination conditions, not setting
args.
As Dave said, it is always best to include the settings you want FIND
to
use.

--
Regards,
Tom Ogilvy


"Dave Peterson" wrote in message
...
.Find is one of those methods that remember the settings the last
time
it
was
used--either via code or via the userinterface.

If you look at the help for .find, you'll see all the options that
can
be
specified. (This is from xl2003.)


expression.Find(What, After, LookIn, LookAt, SearchOrder,
SearchDirection,
MatchCase, MatchByte, SearchFormat)

It may be better to specify all the options that are available than
to
rely on
having the settings the way you want.

(Tom copied the example from the help -- which is less than robust.)

Jim May wrote:

Tom.
This code finds and highlights all cells that include "2"
anywhere
within
the cell, like "1234". But what if you wanted only cells with the
value
"2".
Tks,

"Tom Ogilvy" wrote in message
...
Here is the help example for the FIND method:

This example finds all cells in the range A1:A500 that contain
the
value 2
and makes those cells gray.

With Worksheets(1).Range("a1:a500")
Set c = .Find(2, lookin:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Interior.Pattern = xlPatternGray50
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <
firstAddress
End If
End With

--
Regards,
Tom Ogilvy


"dee2417"
wrote
in
message
...

hi
I want to find some data in excel sheet and want to change the
selected
data into a different color how to do it with a macro

Thanx in advance
dee


--
dee2417


------------------------------------------------------------------------
dee2417's Profile:
http://www.excelforum.com/member.php...o&userid=24587
View this thread:
http://www.excelforum.com/showthread...hreadid=381794




--

Dave Peterson

dee
 

glad to help

--
Don Guillett
SalesAid Software

"Jim May" wrote in message
news:vWgve.67561$Fv.28257@lakeread01...
tks Don;

"Don Guillett" wrote in message
...
OR
Sub Fooo()
With Sheet11.Range("a1:a15")
.NumberFormat = "General"
Set c = .Find(2, lookat:=xlWhole)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Interior.ColorIndex = 6
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
.Style = "Comma"
End With
End Sub

--
Don Guillett
SalesAid Software

"Jim May" wrote in message
news:pDbve.67533$Fv.13324@lakeread01...
Also, notices that if my range is formatted numbers (comma, 2) the
Macro
yields nothing;
If I change formatting the General (Control+Shift+~) macro paints my
four
2's. Find method must be sensitive to formatting,,, hummmm

My Code:
Sub Foo()
With Worksheets(1).Range("a1:a15")
Set c = .Find(2, Lookat:=xlWhole, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Interior.ColorIndex = 6
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
End Sub




"Tom Ogilvy" wrote in message
...
Just to add:
It is robust enough, but less than comprehensive; however it is
always
readily available as a consult for a generalized algorithm. It does
have
some warts, for example the loop termination conditions, not setting
args.
As Dave said, it is always best to include the settings you want
FIND
to
use.

--
Regards,
Tom Ogilvy


"Dave Peterson" wrote in message
...
.Find is one of those methods that remember the settings the last
time
it
was
used--either via code or via the userinterface.

If you look at the help for .find, you'll see all the options that
can
be
specified. (This is from xl2003.)


expression.Find(What, After, LookIn, LookAt, SearchOrder,
SearchDirection,
MatchCase, MatchByte, SearchFormat)

It may be better to specify all the options that are available
than
to
rely on
having the settings the way you want.

(Tom copied the example from the help -- which is less than
robust.)

Jim May wrote:

Tom.
This code finds and highlights all cells that include "2"
anywhere
within
the cell, like "1234". But what if you wanted only cells with
the
value
"2".
Tks,

"Tom Ogilvy" wrote in message
...
Here is the help example for the FIND method:

This example finds all cells in the range A1:A500 that contain
the
value 2
and makes those cells gray.

With Worksheets(1).Range("a1:a500")
Set c = .Find(2, lookin:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Interior.Pattern = xlPatternGray50
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <
firstAddress
End If
End With

--
Regards,
Tom Ogilvy


"dee2417"

wrote
in
message
...

hi
I want to find some data in excel sheet and want to change
the
selected
data into a different color how to do it with a macro

Thanx in advance
dee


--
dee2417



------------------------------------------------------------------------
dee2417's Profile:

http://www.excelforum.com/member.php...o&userid=24587
View this thread:
http://www.excelforum.com/showthread...hreadid=381794




--

Dave Peterson