Draw border above specific text string

[Rashid Khan]

Hello All,
I am using Office XP and have the following problem:

I have many thousand Rows of Data in a Sheet.
I wish to make a Selection and run the macro which should draw a border in
red colour above a text string which is written in D1 for eg Membership

The border in red colour should be drawn on the row above the word
Membership in the Sheet. Pls note that the macro should run for any text
string entered in D1 as a part only.. I mean it should be treating Member,
Membership, Membership No. etc etc... all as one. Because my Sheet has a
value Membership No. xxxx (where xxxx differs for each record)

Any help or suggestions
Can this be achieved?
Many thanks in advance

Rashid Khan

 

[Rashid Khan]

Hi Mark,
Yes u r right.
Suppose their is a string in D1 which is Membership. Then the macro should
draw a line above all the rows which has this string (in part or in full)
Rashid Khan

 

[Mark Thorpe]

I think you just need to check each cell using the InStr function to see if
the string in D1 is a substring. See if this works for you:

Sub Macro1()

Dim sSearch As String
Dim lRowCount As Long
Dim iColCount As Integer
Dim lRow As Long
Dim iCol As Integer

lRowCount = ActiveSheet.UsedRange.Rows.Count
iColCount = ActiveSheet.UsedRange.Columns.Count
sSearch = Cells(1, 4).Value

For lRow = 2 To lRowCount
For iCol = 1 To iColCount
If InStr(Cells(lRow, iCol).Value, sSearch) > 0 Then
With Cells(lRow, iCol).Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 3
End With
End If
Next iCol
Next lRow
End Sub

 

[Rashid Khan]

Hello Mark,
Works like a charm. Thanks a million. You have saved me lots of hours

Rashid Khan