With active cell

[Guest]

hi i am extremely new to vba and need some help. I have recorded a macro where i perform a search within a selected range. the code ends ".activate" when it has found the specified text. now i want to apply some formatting to the cells in that row. I am happy to use the offset function (unless there is an easier way) but how do i make it so it is applied to the active cell so i can continue with "selection." i suppose i want to make it a relative reference not "Range("A13:E13").Select
does that make sense to anyone

I would appreciate any help as i am an extreme novice who is losing confidence fast
thank
kevin

 

[Don Guillett]

As usual, post your coding efforts for comments

--
Don Guillett
SalesAid Software
(e-mail address removed)

hi i am extremely new to vba and need some help. I have recorded a macro

where i perform a search within a selected range. the code ends ".activate"
when it has found the specified text. now i want to apply some formatting to
the cells in that row. I am happy to use the offset function (unless there
is an easier way) but how do i make it so it is applied to the active cell
so i can continue with "selection." i suppose i want to make it a relative
reference not "Range("A13:E13").Select"

 

[Medemper]

'Didn't know if you needed the first line relative or just the stuff below


Range("B4:B18").Select
Selection.Find(What:="Mr Smith", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False).Activate
With Range(Cells(ActiveCell.Row, 1), Cells(ActiveCell.Row, 5))
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeTop).Weight = xlThin
.Borders(xlEdgeTop).ColorIndex = xlAutomatic
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeBottom).Weight = xlThin
.Borders(xlEdgeBottom).ColorIndex = xlAutomatic
.Font.Bold = True
End With




Hope this makes it a little clearer.

Range("B4:B18").Select
Selection.Find(What:="Mr Smith", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False).Activate
'make relative
'Active cell.Select
'Range("A13:E13").Select
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Font.Bold = True

 

[Bob Phillips]

Kevin,

Is this what you want?

Range("B4:B18").Find(What:="Mr Smith", _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False).Activate
'make relative
'Active cell.Select
'Range("A13:E13").Select
With Selection.EntireRow
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
.Font.Bold = True
End With


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Don Guillett]

If I understood you correctly, you wanted to find Mr. Smith and format a
ANOTHER cell based on that location.

Sub FormatOffset()
Range(Columns("b").Find("Mr Smith").address).Offset(1, 2).Select
myBorders = Array(, xlEdgeLeft, xlEdgeTop, xlEdgeBottom, xlEdgeRight)
For i = 1 To UBound(myBorders)
With Selection.Borders(myBorders(i))
.LineStyle = xlContinuous
.Weight = xlMedium
End With
Next
Selection.Font.Bold = True
End Sub

to do range("a foundrow:e foundrow"), use this

Sub Formatfoundrow()
x = Columns("b").Find("Mr Smith").Row
MsgBox x
Range(Cells(x, 1), Cells(x, 5)).Select
myBorders = Array(, xlEdgeLeft, xlEdgeTop, xlEdgeBottom, xlEdgeRight)
For i = 1 To UBound(myBorders)
With Selection.Borders(myBorders(i))
.LineStyle = xlContinuous
.Weight = xlMedium
End With
Next
Selection.Font.Bold = True
End Sub