Highlight text in a cell after x characters

    I have a spreadsheet where certain (multiple) cells have length guidelines for character length within them.


    I have been scouring the web to find some VBA to highlight the text that exceeds the specified length guideline.


    eg.
    Max Characters = 25
    Cell text = The quick brown fox jumped over the lazy dog.



    However, I would like to HIGHLIGHT the text (like a yellow highlighter) with no other change to the formatting.



    Since there are multiple fields in this sheet with different character guidelines I figured approaching it from a function perspective would be my best bet. I could then call the function (sub) with the cell and character number as variables.


    Here's what I have which doesn't really seem to work at all (and tries to bold the text instead of highlighting).
    I think it is because I'm not using the right syntax to identify the cell in the sub call but it should illustrate what i'm trying to do.


    Code
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)Call Check_Length("H31", 125)Call Check_Length("H43", 55)End SubSub Check_Length(theCell, characterCount)        textLength = Len(theCell)           'Set BOLD        Range(theCell).Characters(characterCount + 1, textLength).Font.Bold = TrueEnd Sub

    Re: Highlight text in a cell after x characters


    I don't know what you mean by highlight.


    If you mean the background color, I think that is for the whole cell only not just for certain characters.


    This code should make the characters red, yellow doesn't show up too well.


    Code
    Sub Check_Length(theCell, characterCount As Long)
     Dim textLength As Long
    textLength = Len(Range(theCell))
     
     'Set BOLD
    Range(theCell).Characters(characterCount + 1, textLength - characterCount).Font.Color = vbRed
End Sub

    Bruce :cool:

    Re: Highlight text in a cell after x characters


    Ahhh ok. yeah I meant yellow highlighting as in word when selecting and highlighting the individual text's background. I see that formatting style isn't available in a cell.


    your example above... do I still call the sub with


    Check_Length ("H31", 125)


    As I was doing in my example??

    Re: Highlight text in a cell after x characters


    I wrote it to work with your call, so yes.


    You are running it from the selection change event which means every time you select a cell other than the cell that was already selected the code is triggered.


    The argument you see in the title "Target" is the cell that was selected, so you may want to use this to your advantage.


    You could have a group of cells for example A1:A7 and in these cells you could have the cell you are interested in H31 for example and in B1:B7 you could have the character counts, so for example:
    A1 =H31 and B1 = 125.


    I can write you code that when you select anyone of the cells A1:A7 the code first checks to make sure the cell you selected was one of those cells A1:A7 just so the code doesn't run on every cell you click on the sheet, it would then pass the values in A1 and B1 to the other procedure and there you go.


    A1:A7 doesn't have to be those cells and it could be more cells if you have a longer list.


    There's a lot of possibilities.


    You could also have two input boxes, the first where you highlight all the cells you are interested in and the second where you type the number of characters you want to check and it will work on all the cells you selected.

    Bruce :cool:

    Re: Highlight text in a cell after x characters


    Understand.
    It would make more sense to only trigger the event when the appropriate cell is selected (or deselected). It would then check against the corresponding character count for that 'active' cell or row only.


    Let me look at the sheet(s) I have and determine if all character counts are listed in the same row as the content field and if the columns are consistent across sheets before going forward.


    I have about 100+ of these content sheets I would like to add this validation to and want to make sure it's not a one-off macro for each one.


    Thanks for the help so far.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login