dcsimg
Results 1 to 6 of 6

Thread: highlighting the characters in a text-excel

  1. #1

    Thread Starter
    Fanatic Member merhaba's Avatar
    Join Date
    Sep 2002
    Location
    Istanbul,Bartin
    Posts
    601

    highlighting the characters in a text-excel

    Say I have a text or a sentence in A1, and I am entering some letters in cell B1 to see if those letters are in the text.
    and if the text contains any of the letter(s) ,let it/them be highlighted in color yellow as soon as I write them.
    thanks
    Attached Images Attached Images  

  2. #2
    Lively Member Peekay's Avatar
    Join Date
    Sep 2006
    Location
    Witbank, South Africa
    Posts
    124

    Re: highlighting the characters in a text-excel

    This should help. Get the character/s with the instr() function and then highlight it:

    https://www.mrexcel.com/forum/excel-...rev-excel.html

    PK

  3. #3
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    9,479

    Re: highlighting the characters in a text-excel

    Moved the question to the office development forum.

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,783

    Re: highlighting the characters in a text-excel

    Code:
    For c = 1 To Len(Range("e2"))
        For r = 1 To Len(Range("a2"))
            If Range("a2").Characters(r, 1).Text = Range("e2").Characters(c, 1).Text Then Range("a2").Characters(r, 1).Font.Color = vbRed
        Next
    Next
    i tested this, change ranges and colour to suit
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  5. #5

    Thread Starter
    Fanatic Member merhaba's Avatar
    Join Date
    Sep 2002
    Location
    Istanbul,Bartin
    Posts
    601

    Re: highlighting the characters in a text-excel

    I think ı need more help as where to paste this code.. My text is in A1:A50 and my entry box/cell is B2 I pasted it to a module but it did not work..How do I modify "e2" and "a2" ?

  6. #6
    PowerPoster
    Join Date
    Dec 2004
    Posts
    23,783

    Re: highlighting the characters in a text-excel

    as soon as I write them
    I pasted it to a module but it did not work
    there is no keypress or similar events for excel cells, but you can use the worksheet change event to colour the characters as soon as you leave the cell
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Integer, c As Integer
    If Not Intersect(Target, Range("a1:a50")) Is Nothing Then  ' only run the code for range a1:a50
        For c = 1 To Len(Range("b2"))
            For r = 1 To Len(Target)
                If Target.Characters(r, 1).Text = Range("b2").Characters(c, 1).Text Then Target.Characters(r, 1).Font.Color = vbRed
            Next
        Next
    End If
    End Sub
    you can change to any colour you like. or use a colorindex, but yellow is almost invisible
    this is tested to work without errors
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width