Format row based on text length

[Closed]
Report
Posts
2
Registration date
Wednesday August 19, 2015
Status
Member
Last seen
August 21, 2015
-
Posts
1320
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
October 8, 2021
-
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
1320
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
October 8, 2021
239
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.
Posts
2
Registration date
Wednesday August 19, 2015
Status
Member
Last seen
August 21, 2015

awesome, thank you very very much.

Tomas
Posts
1320
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
October 8, 2021
239
Hello Tomas,

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

Cheerio,
vcoolio.