Need to 'Bold' & 'Underline specific characters in a field

Hello,

I'm in the middle of a project & trying to find a couple shortcuts that in the end would (hopefully) reduce data entry errors. Labor Distributions are posted weekly to QuickBooks. At some point these entries will be imported; however, the entries still need to be reviewed for correctness. Some project names are 50+ characters. All projects are assigned a "DC" job #; for instance - enter 'DC 1035' in QB & "Patterson Equipment:Internal Research & Dev:DC 1035 Monster Tools" would come up.

So far, I have been making the 'DC ####' bold & underlined on each line in the entries. I have a very long way to go. I wrote a macro that would bold & underline. I tried changing the Start to :='DC', Length:=5 - This did not work, see below.

MY MACRO
With ActiveCell.Characters(Start:='DC', Length:=5).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Range("G42").Select
End Sub

Would it be possible to write a VBA code to do the following:

Select any column,
search for "DC ####" within the project names
Bold & Underline ONLY the "DC ####" portion

Any help would be very appreciated,

Thank you,
-Katie

:='DC' <---- will not work for many reasons. First, you would normally use double quotes not single (ie :="DC"). But that still will not work since I believe the Characters Object requires a numerical value there (I could be wrong though).

However, there are a couple of ways around this. Is the DC always located in the same start position? In other words does the DC always exist in the same text position, like the fifth character? If yes then you could just make the start "5" and it should work provided you actually tell your recorded macro to change the .fontstyle to Bold and the .underline to Single.

If the character position of "DC" is different each time, there is a feature called Instr() that could probably be utilized to locate the start character start of "DC". I will see what I can work up as an example. Some more information might be helpful though.

Try this:

Please Login or Register  to view this content.

Select the Cell(s) and run the code.

Please Login or Register  to view this content.

Both Scripts work beautifully...A huge THANK YOU to both of you!

Your script works great! Only...how does it know the length? AND In .Pattern = "\bDC \S+, what is the b & the \S+ mean or do? This is basically how I've learned to at least understand VBA script, even if I can't write it on my own (lol).

Thanks again

.Pattern = "\bDC \S+"

\b = boundary of string like space, beginning of sentence, end of sentence or comma/column/hephen etc.

DC = DC

\S+ = any charcters except space.

So, it will match to (boundary)DC(space)(any cacharacters except space).

That means, it doesn't matter how long the last characters.

You know what I mean?

Very Cool, thank you for explaining.