Format Cell based on # of characters...

[tdfsu]

I want to change the background color of a cell if the number of characters is 255 or above. But I do not have room to add cells to count using LEN. I have a large range of cells, of which, all have a lot of text typed in.

I'd like to do it as a subroutine. I found this code, but I cannot get it to work.


Sub myAddColor()
' Add color if Range("A1:D100") greater than 255, if 255 remove color.
' By Joe Was
'
Range("A1:D100").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="255"
Selection.FormatConditions(1).Interior.ColorIndex = 8
Range("A1").Select
End Sub



I'd greatly appreciate any help,
Jason.

 

[Tom Urtis]

Does this do what you want?

Sub myAddColor2()
Range("A1:D100").Select
With Selection
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=LEN(A1)>255"
.FormatConditions(1).Interior.ColorIndex = 8
End With
End Sub

 

[tdfsu]

That's it. Thanks Tom.