Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Sep 2004
    Location
    Sacramento, California, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Conditional Formatting? (ExcelXP)

    Hi,

    I am looking for a way to have Excel look at a cell and see if it has the letter "t" in it and it so turn that letter "t" red. Then make it flexible so the user can find any user supplied letter and turn it red. I have looked at conditional formatting and tried several options on just the one letter and not had much success. I also looked at the Excel functions and did not see a combination I could use.
    Any help or any direction to work with would be appreciated.

    Carla

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Conditional Formatting? (ExcelXP)

    You can not do it with conditional formatting. You could format all the text if it contained a "t", but not just the "t".

    A workaround could be done with code. You could add this to a module:
    <pre>Sub ColorMe(rCell As Range, sSearch As String, _
    lColor As Long)

    Dim sWord As String
    Dim iStart As Integer
    Dim iEnd As Integer
    Dim iWordLen As Integer
    Dim iLen As Integer

    iLen = Len(sSearch)
    With rCell
    sWord = .Cells(1).Value
    iWordLen = Len(sWord)
    iStart = InStr(sWord, sSearch)
    Do While iStart > 0 And iStart < iWordLen
    iEnd = iStart + iLen
    .Characters(Start:=iStart, Length:=iLen). _
    Font.Color = lColor
    iStart = InStr(iEnd, sWord, sSearch)
    Loop
    End With
    End Sub</pre>


    The routine takes a cell reference, a string to search for and a color and changes it.

    You could call it with a routine like this:
    <pre>Sub ColorWithinSelection()
    Dim rCell As Range
    For Each rCell In Selection
    ColorMe rCell, "t", vbRed
    Next
    Set rCell = Nothing
    End Sub</pre>


    Select the range and run the "ColorWithinSelelction" routine to color all the "t"s in it. It is case sensitive so if you want to also do a "T" you could add the line:
    <pre>ColorMe rCell, "T", vbRed</pre>


    to the above to do both. Or you could have different colors for each case. If you wanted it automatic you could put it into a change routine (in the sheet object):
    <pre>Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim rCell As Range
    For Each rCell In Target
    ColorMe rCell, "t", vbRed
    Next
    Set rCell = Nothing
    End Sub</pre>


    Of course you could limit it to a particular range (eg A1:A10) on the sheet rather than the entire sheet:

    <pre>Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim rCell As Range
    Dim rng As Range
    Set rng = Range("A1:A10")
    If Not Intersect(Target, rng) Is Nothing Then
    For Each rCell In Intersect(Target, rng)
    ColorMe rCell, "t", vbRed
    Next
    End If
    Set rCell = Nothing
    Set rng = Nothing
    End Sub</pre>


    Hope this helps,
    Steve

  3. #3
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting? (ExcelXP)

    OK Carla,
    Might be barking completely up the wrong tree, but here is my submission for the worstest, clunkiest and most pathetic attempt at a solution possible!
    Create loads of extra columns, find the postion of the first t if there is one (replacing search by find makes it case-sensitive if that's what you want), then re-assemble the whole string into three columns with the narrowest one just wide enough for the t or T, and conditional format on that. (or do a global format on it: a blank cell formatterd red is, er, blank.)
    Example attached.
    I suspect we're going to get some elegant vba solutions full of for, next, case select, with and end with. Watch this space!
    For any character not T or t, put the target character in a separate cell and refer to that in the formulas.

  4. #4
    4 Star Lounger
    Join Date
    Sep 2004
    Location
    Sacramento, California, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting? (ExcelXP)

    Solution works as does the previous post. Thank you very much for the assistance.

    Carla

  5. #5
    4 Star Lounger
    Join Date
    Sep 2004
    Location
    Sacramento, California, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting? (ExcelXP)

    Steve,

    Exactly what I needed. I did not see a way in conditional formatting, and just could not get a start with anything else.
    thank you for the assistance.

    Carla

Posting Permissions

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