×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Are you an
Engineering professional?
Join Eng-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# Howto to search for a string in a sheet, that came from another sheet

## Howto to search for a string in a sheet, that came from another sheet

(OP)
Hi,

I'm stuck writing a Excel97 macro that takes a string from sheet1 and I want to search for that string in sheet2. For every instance of that string in sheet2 I want to change the textcolor, until I have found them all in sheet2. I only want to change the text color of the string that I'm searching for and not the complete text of a cell in which the string was found.

Could someone give me some pointers on how to setup my macro in such a way that th above will work? Thanks very much.

Alwyn

The Netherlands.

### RE: Howto to search for a string in a sheet, that came from another sheet

Excell has a rather fancy function called "conditional formatting" hidden under the format menu itim.

Her you can set up a formatting that depends on the cell contens.

You can actually "record" a macro that will do what you require. I have done just that but since i dont know what cells are "free" and is not then you may have to "redo" it. But its very simple:

Sub Macro8()
'
' Macro8 Macro
' Macro recorded 31-05-2002 by Morten Andersen
'

'
Selection.Copy
ActiveWindow.ActivateNext
Range("C1").Select
ActiveSheet.Paste
Cells.Select
Application.CutCopyMode = False
Selection.FormatConditions.Delete
Formula1:="=$C$1"
Selection.FormatConditions(1).Interior.ColorIndex = 3
End Sub

This macro takes the value in the cell you are looking for and moves it to the sheet where you want to test (assumed to be "the next sheet". Then it "conditional formats" the entire sheet to change the colour of the cell with this exact value to red.

Many other conditional options than "exact match" exists.

Check it ouy yourself.

Best Regards

Morten

### RE: Howto to search for a string in a sheet, that came from another sheet

Oh yes:

Calling "conditional formatting" from a macro is of course not required. Now that you know that this function exists it may be easier just to use it directly.

Best Regards

Morten

### RE: Howto to search for a string in a sheet, that came from another sheet

(OP)
Hi Morten,

Thanks for your advice. I was not aware of funtionality of 'conditional formatting' . It does almost what I'm searching for, as far as I can see it changes the color of the complete cell, but I would like only to change the color of the string that I'm searching for, and exactly that is what I can not get to work, selecting a string in a cell that (could) contain more strings than the string that I am searching for .........

Thanks,
Alwyn

### RE: Howto to search for a string in a sheet, that came from another sheet

You can set the format to anything you like (i used background colour but font colour changes just as easily). Remeber that the cell containing the values must be selected before applying conditional format (as with all other format operations). If you want the conditional format to be valid for all cells just click the upper left cornor of the sheet to select all cells

You can add as many conditions (with different formatiings) as you require.

If you have more than 1 value then maybe the macro (with a for next loop or similar) is worth the trouble.

I dont really understand what you mens by "selecting a string in a cell that (could) contain more strings than the string that I am searching for .........". If you dont know what to select then you have a serious problem. If you know a "separator" (comma, semi colon, carriage return or the like) then you should pretty easy be able to set a "search & select" function up in VBA.

Best Regards

Morten

### RE: Howto to search for a string in a sheet, that came from another sheet

(OP)
Ho Morten,

I think a example would be more clear than to use words:

The string I'm searching for is: PMSfu12345

Now for example if I find a cell that contains the following:

pmsfu54321,PMSfu12345, PMSim34732 PMSfu98457

How can I change the color of only PMSfu12345 in the above string using a macro. I am able to find cells containing the string, but I'm not able to change the color of only that string that I'm searching for. I hope this is more clear to you? Thanks.

Alwyn

### RE: Howto to search for a string in a sheet, that came from another sheet

Ok thats different.

well theres only the hard way here i belive.

This little piece dose this (but no more).

It assumes that your traget value is in D5 and that your test value is in the cell below (d6). Its most like not this way but you will have to adapt the code then.

Sub findspecial()
Range("D5").Select
target_val = ActiveCell.Value
ActiveCell.Offset(1, 0).Select
testval = ActiveCell.Value
target_len = Len(target_val)
asteps = Len(testval) - target_len + 1
For n = 1 To asteps
If Mid\$(testval, n, target_len) = target_val Then
hitme = True
fromhere = n
End If
Next n
If hitme = True Then
With ActiveCell.Characters(Start:=fromhere, Length:=target_len).Font
.ColorIndex = 3
End With
End If
End Sub

Best Regards

Morten

### RE: Howto to search for a string in a sheet, that came from another sheet

(OP)
Hi Morton,

The piece of code you supplied, is exactly what I needed. Thank you very much, now I can proceed with my spreadsheet macro. Again thanks.

Best regards,
Alwyn

### RE: Howto to search for a string in a sheet, that came from another sheet

If you are importing from another source (text file) dont use fixed width but change this to comma separtor. Then you will get each value in the string in a seperate cell. Much easier to work with.

Best Regards

Morten

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

#### Resources

Low-Volume Rapid Injection Molding With 3D Printed Molds
Learn methods and guidelines for using stereolithography (SLA) 3D printed molds in the injection molding process to lower costs and lead time. Discover how this hybrid manufacturing process enables on-demand mold fabrication to quickly produce small batches of thermoplastic parts. Download Now
Examine how the principles of DfAM upend many of the long-standing rules around manufacturability - allowing engineers and designers to place a partâ€™s function at the center of their design considerations. Download Now
Taking Control of Engineering Documents
This ebook covers tips for creating and managing workflows, security best practices and protection of intellectual property, Cloud vs. on-premise software solutions, CAD file management, compliance, and more. Download Now

Close Box

# Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!