×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • 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.

Students Click Here

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

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
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        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

one small piece of advice:

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.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login



News


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:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close