Highlight text (interior color)

[Abdul]

Hello!

Is it possible to chane the interior color of a text depending upon the
length of text?
If the text is just fitting the cell we can change the interior color.
But if the text extends to the next cell how we can identify this? I
dont want to change the column width of course.

so what i am looking for is a way to identify the text length and if it
streches to next cells then change the interior color of those cells
too.

Thanks

 

[Guest]

Hi Abdul - try this,- its not perfect, but somthing to work with

Sub Fit()
Dim r As Range
Dim u, x
For Each r In ActiveSheet.UsedRange
u = 0
For x = 1 To Len(r.Value)
If Mid(r.Value, x, 1) = UCase(Mid(r.Value, x, 1)) And Mid(r.Value, x, 1) <>
"" Then u = u + 1
Next
If r.ColumnWidth < Len(r.Value) + Int((u / 3)) And r.Value <> "" Then
r.Interior.ColorIndex = 3
If r.ColumnWidth > Len(r.Value) + Int((u / 3)) Or r.Value = "" Then
r.Interior.ColorIndex = xlNone
Next
End Sub

 

[Peter T]

There are some methods here
http://www.dicks-blog.com/archives/2005/05/16/1131/

Mine is in posts 4, 5 & 6, I had problems posting the code, firstly

Dim va(0 to 8) arrived as Dim va(0 to smiley

then the <= sign in what should have been
Do While w <= wd
terminated posts 4 & 5

knowing that I hope you can piece it together.

Regards,
Peter T