Text formatting

[kingerd]

From a column of entries I want to single out text in a gven cell and format that text only using VB

Here is what I have in VB

Private Sub replaceCSI()
Worksheets("PBR").Protect UserInterfaceOnly:=True
With Application.ReplaceFormat.Font
.FontStyle = "Bold"
.Subscript = False
.ColorIndex = 3
End With
Columns("A:A").Select
Selection.Replace What:="CSI", Replacement:="Critical Safety Item", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=True
End Sub

Thanks for any help.
kingerd

[wdwells]

[quote name='kingerd' post='791874' date='02-Sep-2009 17:05']From a column of entries I want to single out text in a gven cell and format that text only using VB

Here is what I have in VB

Private Sub replaceCSI()
Worksheets("PBR").Protect UserInterfaceOnly:=True
With Application.ReplaceFormat.Font
.FontStyle = "Bold"
.Subscript = False
.ColorIndex = 3
End With
Columns("A:A").Select
Selection.Replace What:="CSI", Replacement:="Critical Safety Item", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=True
End Sub

Thanks for any help.
kingerd[/quote]
Welcome to the Lounge. See if this post is helpful.

[bradjedis]

Hey folks!

I am back!!! I happen to work with the originator of this thread, and we have a "almost" solution to the problem, however there is one thing causing issues.

Background:
The issue is that we needed to structure down through a range of cells in a column, find any instance of s specific string of data, and change the format of only that data. This was solved. However, when the code encounters an "empty" cell, it stops looking at any other cells. Now, most likely, there will be empty cells to contend with.

How do we check for the empty cells, and get the code to continue? Have tried a variety of things, and no luck.

Here is the code, and the earliest cell to start looking in is Q13. Ending cell is variable. I think I can clean up to code to find the endpoint (last row) but for now, just need to solve the empty cell issue.
Thanks in advance,
Brad


Sub SetColor()
Dim cell As Range, i As Long
Dim Rng As Range
Dim txt As String

Set Rng = Range("Q13", Range("Q13").End(xlDown))

For Each cell In Rng
txt = cell.Value
i = InStr(1, txt, "Critical Safety Item", 1) ' 1 for Text (case insensitive)
If i <> 0 Then
cell.Characters(i, 20).Font.ColorIndex = 3
cell.Characters(i, 20).Font.FontStyle = "Bold"
cell.Characters(i, 20).Font.Subscript = False
End If
Next cell

End Sub

[StuartR]

The line

Set Rng = Range("Q13", Range("Q13").End(xlDown))

will select all cells from Q13 down to the first blank cell. If you want to select from Q13 down to the last cell in column Q that has something in then try

Set Rng = Range("Q13", Range("Q65535").End(xlUp))

This will search up from the bottom of the column to find the first cell with content.

[bradjedis]

I see that, but I think we need to test for cell content or not. If not content, then continue looking. When cell has content, then perform actions. Continue until cells in range have been looked at.


Brad

[StuartR]

[quote name='bradjedis' post='792142' date='04-Sep-2009 15:39']I see that, but I think we need to test for cell content or not. If not content, then continue looking. When cell has content, then perform actions. Continue until cells in range have been looked at.


Brad[/quote]

Did you test this? I have just tried it and it seems to cope with empty cells fine.

[bradjedis]

Talk about a "duh" moment....

Changed the Range statement to be "Q:Q" and all is fine...

Talk about easy...

Thanks
Brad