PDA

View Full Version : Solved: Make text in a cell bold



fredlo2010
05-24-2012, 09:26 PM
Hello,

I am trying to make the text in an specific cell bold. The text is inside a table and for some reason every time I use a macro that copies from another sheet and pastes values here, conditional formation stops working. Actually the range that i set it to cover first splits in two to exclude the table.

The words I want bold are " Systems", "Cookies", "Items"

I am a total newbie to VBA I am trying to learn as I go, I tried to create a code but it gives me an error.

Here is the code I have

Sub AddSystems(varColumns As String)


Call ClearContents

Sheets("Appendix Data").Range("A6:A406").Value = Sheets("Systems").Range(varColumns & 2, varColumns & 402).Value

With Sheets("Appendix Data").Range("A1:A1000")
.Find.Value = "Systems"
.Font.Bold = True
End With
End Sub

thanks for the help

Bob Phillips
05-25-2012, 02:04 AM
When you copy, it will copy the format unless you code around it.

You bold code will bold the whole range, not just a cell with Systems in it, as it is all encompassed within the with statement.

fredlo2010
05-25-2012, 05:43 AM
Thanks,

I used the macro recorder and i got this piece of code


Range("A1:A10000").Find(What:="Systems", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Selection.Font.Bold = True


But the thing is that

1 it uses selection which i am trying to avoid ( it should speed up the macro)
2 it bolds only the first instance of the word not the rest.

I am really trying here but VBA is hard. I will go back to my book to try to find a solution. I tried to look it up on the web but there is no other thread regarding the matter.

Thanks

Bob Phillips
05-25-2012, 05:54 AM
You can use Excel search and replace

Sub UpdateData()

Application.ReplaceFormat.Font.FontStyle = "Bold"
Call enbolden(Range("A1:A10000"), "Systems")
Call enbolden(Range("A1:A10000"), "Cookies")
Call enbolden(Range("A1:A10000"), "Items")
End Sub

Private Function enbolden(rng As Range, ByVal lookup As String)

rng.Replace What:=lookup, _
Replacement:=lookup, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=True
End Function

fredlo2010
05-25-2012, 09:47 AM
this code works perfectly thanks a lot

::):