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

[BlackSeal]

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.

 

[MortenA]

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&quot.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

 

[MortenA]

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

 

[BlackSeal]

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

 

[MortenA]

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

 

[BlackSeal]

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

 

[MortenA]

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&quot.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

 

[BlackSeal]

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

 

[MortenA]

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