Results 1 to 7 of 7

Thread: Text formatting

  1. #1
    New Lounger
    Join Date
    Sep 2009
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
    Attached Files Attached Files

  2. #2
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [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.
    Regards
    Don

  3. #3
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  4. #4
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts
    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.

  5. #5
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  6. #6
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts
    [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.

  7. #7
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Talk about a "duh" moment....

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

    Talk about easy...

    Thanks
    Brad

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •