format row based on text lengthClosed

Question

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

vcoolio · Answer

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).RowSheet1.SelectFor 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 IfNextEnd 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.

Llupo01 · Answer

awesome, thank you very very much. 

Tomas

vcoolio · Answer

Hello Tomas,

You're welcome. I'm glad that I could help.

Cheerio,
vcoolio.

Format row based on text length

3 responses

Similar discussions

Subscribe To Our Newsletter!