Format row based on text length

Closed
Report
Posts
2
Registration date
Wednesday August 19, 2015
Status
Member
Last seen
August 21, 2015
-
Posts
1329
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
January 18, 2022
-
Hello guys,

Can you pls help me? I am beginner in VBA and I need to sort out one macro. I have table and need to have highlited row with color, but not entire row, just from columns A to J, when text in one of the cells in column A is longer than 4 digits-letters...I dont want to use conditional formating. Can somebody pls advice if possible? Thank you very much in advance, appreciate any help from your side.

This is what I found and its working, but just for one cell. I would like to highlight row from A to J columns..

`Sub Highlight()
Dim c As Range
Dim LR As Integer
Dim intCell As Long
LR = Worksheets("Sheet1").Cells(Rows.Count, 6).End(xlUp).Row
For intCell = 1 To 1
For Each c In Range("A1:A1000" & LR).Cells
If Len(c.Value) > 4 Then
c.Cells(intCell).Interior.Color = vbYellow
End If
Next
Next
End Sub`

brgds,

Tomas

3 replies

Posts
1329
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
January 18, 2022
241
Hello Tomas,

Try the following trimmed down code:-

Sub Highlight()

Dim LR As Long
Dim cell As Variant

LR = Range("A" & Rows.Count).End(xlUp).Row

Sheet1.Select

For Each cell In Range("A1:A" & LR)
If Len(cell) > 4 Then
Range(Cells(cell.Row, "A"), Cells(cell.Row, "J")).Interior.Color = vbYellow
End If
Next

End Sub



You can have a look at my test work book at the following link:-

https://www.dropbox.com/s/rqqn7svx1o8i50d/Llupo01.xlsm?dl=0

to see if it does what you would like.

I hope that this helps.

Cheerio,
vcoolio.
0
Posts
2
Registration date
Wednesday August 19, 2015
Status
Member
Last seen
August 21, 2015

awesome, thank you very very much.

Tomas
0
Posts
1329
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
January 18, 2022
241
Hello Tomas,

You're welcome. I'm glad that I could help.

Cheerio,
vcoolio.
0