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

I'm not sure I follow exactly what you're trying to do. Is it that you want
a routine which will draw a border above any cell which contains text which
includes, as a substring, whatever text is in cell D1?


"Rashid Khan" wrote in message
...
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

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" wrote in message
...
I'm not sure I follow exactly what you're trying to do. Is it that you
want
a routine which will draw a border above any cell which contains text
which
includes, as a substring, whatever text is in cell D1?


"Rashid Khan" wrote in message
...
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

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

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

Rashid Khan
"Mark Thorpe" wrote in message
...
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