Addendum to Format answer from JE

  • Thread starter Thread starter Regina
  • Start date Start date
R

Regina

I'm sorry, I should've been more specific. When I ran the
macro, the whole cell became bold. What I should've
written is that the PN: #### that needs to be bold is in a
cell with other lines, where we hit Alt Enter as a return
to next line, so it appears like this but all in one cell:
Kit Label 213
Equipment, PN: #######
VXYIMY
Quantity: 8
Can you help?
thanks so much,
Regina
-----Original Message-----
thank you JE, we will give it a whirl.
Sincerely,
Regina
.
Click to expand...
..
 
How do you tell when the part number ends--when you hit the next alt-enter?

If yes, then you could search for the PN: and when you find it, look at the
value in the cell and bold between the PN: and the next alt-enter.

Option Explicit
Public Sub BoldPN()
Const sSEARCH As String = "PN:"
Dim StartPos As Long
Dim EndPos As Long
Dim rFound As Range
Dim sFirst As String

With ActiveSheet.Cells
Set rFound = .Find( _
What:=sSEARCH, _
LookIn:=xlValues, _
LookAt:=xlPart, _
MatchCase:=False)
If Not rFound Is Nothing Then
sFirst = rFound.Address
Do
StartPos = InStr(1, rFound.Value, sSEARCH, vbTextCompare)
EndPos = InStr(StartPos, rFound.Value, vbLf, vbTextCompare)
If EndPos = 0 Then
EndPos = Len(rFound.Value)
End If
rFound.Characters(Start:=StartPos, _
Length:=EndPos - StartPos + 1).Font.Bold = True
Set rFound = .FindNext(after:=rFound)
Loop Until rFound.Address = sFirst
End If
End With
End Sub

If part number can have more stuff on that same line, but it's separated by a
space, you could search for that:

Change:
EndPos = InStr(StartPos, rFound.Value, vbLf, vbTextCompare)
to
EndPos = InStr(StartPos+4, rFound.Value, " ", vbTextCompare)

And if you don't have a nice "ending" character to search for, but the length of
the part number string is always the same:

EndPos = StartPos + 8 'or whatever you need.
 
Back
Top