Conditionally format excess characters in a cell?

[kateb64]

If I have a cell limited to 250 characters, is it possible to conditionally format any characters over that limit? That is, the first 250 would be black, and the extra ones red. I didn't want to separate out into other columns but that's the only solution I can see so far.

 

[Luke M]

You can't conditionally format individual text/words within a cell, but to flag any cell over the limit, you could use a CF formula similar to this:

=LEN(A2)>250

 

[NARAYANK991]

Hi ,


Is it possible to use a macro ?


The Characters property may allow you to do this ?


Narayan

 

[NARAYANK991]

Hi Kate ,


Can you try out the following :


Private Sub Worksheet_Change(ByVal Target As Range)

If Application.Intersect(Target, Range("N6")) Is Nothing Then Exit Sub

Application.EnableEvents = False

Range("N6").Select

length_of_text = Len(Selection)

If length_of_text > 250 Then

Selection.Characters(1, 250).Font.Color = vbBlack

Selection.Characters(251, length_of_text - 250).Font.Color = vbRed

End If

Application.EnableEvents = True

End Sub


I tried it out on the sample text given below , and it works out.


This is a very long sentence of text , just to test the macro which conditionally formats a sentence based on the number of characters exceeding 250 , making the first 250 characters appear in black , while the remaining appear in RED , which I thought could not be done , but which on further thought , could be done.


Narayan