Issue with VBA code - removing highlighting from blank cells.

[dougmarkham]

Hi Folks,

I am working on a project to highlight cells red if they exceed certain character lengths

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]User ID[/TD]
[TD]Name[/TD]
[TD]Address 1[/TD]
[TD]Address 2[/TD]
[TD]Address 3[/TD]
[TD]Address 4[/TD]
[TD]Town / City[/TD]
[TD]Postcode[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]






For instance, I want to use VBA to highlight any cells in the User ID column (col A) if the cells text string > 20 characters; however, if a person then deletes some of the characters so the character string length drops <20, I want the cell's red highlight to disappear. Also, if a person were to clear the contents of a cell that contained >20 (i.e., it's highlighted red already), I want the VBA to remove the red highlight.

I found and adapted some VBA:

Private Sub Worksheet_Change(ByVal Target As RANGE)Dim c As RANGE
Dim LR As Integer
Dim numProbs As Long
Dim sht As Worksheet


Set sht = Worksheets("AddressData")


numProbs = 0
LR = sht.Cells(Rows.Count, "A").End(xlUp).row
For Each c In sht.RANGE("$A$2:$A" & LR).Cells
    If Len(c.Value) > 20 Then
        c.EntireRow.Cells(1).Resize(1, 1).Interior.Color = vbRed
        numProbs = numProbs + 1
    End If
Next
[COLOR=#b22222][B]For Each c In sht.RANGE("$A$2:$A" & LR).Cells[/B][/COLOR]
[COLOR=#0000cd][B]    If c.Value = "" Then[/B][/COLOR]
        c.EntireRow.Cells(1).Resize(1, 1).Interior.Color = xlNone
        numProbs = numProbs + 1
    End If
Next
For Each c In sht.RANGE("$A$2:$A" & LR).Cells
    If Len(c.Value) < 21 Then
        c.EntireRow.Cells(1).Resize(1, 1).Interior.Color = xlNone
        numProbs = numProbs + 1
    End If
Next


If numProbs > 0 Then
    MsgBox "Character limits: Col A (20) - see red cells"
End If

End Sub

The code works, all except the bit that removes highlighting from cells that have their contents cleared (blank cells). Curious thing is that the VBA does its job for cell A2 only. For instance, when you enter a text string of 30 characters, the cell highlights red. When you then clear the contents of cell A2, the red highlight disappears. However, if you do the same process for cell A3 downwards, when you clear the cell contents, the red highlight remains.

Would anybody be able to suggest a way to modify this code (the bit highlighted in red or blue) in order to make it operate in all cells of column A2 downwards?

Kind regards,

Doug.

 

[Fluff]

Rather than use VBA you can do this with conditional formatting if you're interested.

 

[Peter_SSs]

Another thing to consider might be to use Data Validation to stop users entering more than 20 characters in the first place.

 

[dougmarkham]

Rather than use VBA you can do this with conditional formatting if you're interested.

Yes, I'd be interested to learn how to achieve the same using conditional formatting---especially if it's possible to run for multiple columns containing different character length limits?
The main issue with this method we've had so far is that people are pasting the data in with formatting, and it's over-writing the conditional formatting. I'm looking into VBA as a way to fool proof this spreadsheet.


Also, now I've got this VBA puzzle, I'm dying to know why the VBA reference for

 [COLOR=#b22222][B]For Each c In sht.RANGE("$A$2:$A" & LR).Cells
[/B][/COLOR]

isn't working the If statement:

[COLOR=#0000cd][B]If c.Value = "" Then[/B][/COLOR]

. For instance, why does it work when the if statement is:

 [COLOR=#0000cd][B]If Len(c.Value) > 20 Then[/B][/COLOR]

...

 

[dougmarkham]

Another thing to consider might be to use Data Validation to stop users entering more than 20 characters in the first place.

Hi Peter,

This would be ideal, unfortunately, the issue arose because the address data (sometimes hundreds of lines) is being pasted in by other people, so the task is to highlight cells that contain text strings exceeding character limits so they can be selectively modified.

Kind regards,

Doug.

 

[Fluff]

This needs to go on the AddressData sheet

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
Dim LR As Integer
Dim numProbs As Long

numProbs = 0
LR = Cells(Rows.count, "A").End(xlUp).Row
For Each c In Range("A2:A" & LR)
    If Len(c.Value) > 20 Then
        c.Interior.Color = vbRed
        numProbs = numProbs + 1
    Else
      c.Interior.Color = xlNone
    End If
Next
If numProbs > 0 Then
    MsgBox "Character limits: Col A (20) - see red cells"
End If

End Sub

 

[dougmarkham]

This needs to go on the AddressData sheet

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
Dim LR As Integer
Dim numProbs As Long

numProbs = 0
LR = Cells(Rows.count, "A").End(xlUp).Row
For Each c In Range("A2:A" & LR)
    If Len(c.Value) > 20 Then
        c.Interior.Color = vbRed
        numProbs = numProbs + 1
    Else
      c.Interior.Color = xlNone
    End If
Next
If numProbs > 0 Then
    MsgBox "Character limits: Col A (20) - see red cells"
End If

End Sub

Hi Fluff,

This certainly is a more efficient method, thanks!

However, the same glitch is occurring. After cell A3 downward, if you type >20 characters, the cell dutifully turns red. However, if you select that red cell and hit the delete key to clear contents, the cell remains highlighted red. Puzzling as to why the highlighting disappears for cell A2 and A3 but not A4 downwards?

Kind regards

Doug.

 

[Fluff]

Sounds like something else is going on.
Make sure that you have no CF running on col A of that sheet.

 

[dougmarkham]

Sounds like something else is going on.
Make sure that you have no CF running on col A of that sheet.

Hi Fluff,

I removed conditional formatting that was on the header line.
Interesting thing now happens: refreshing the vba highlights cell A1 red despite the reference being A2:A?
Is that not odd?

I started afresh in a new workbook and get the same result: refreshing the vba or adding more than 20 characters then clearing contents makes cell A1 highlight in red, and the cell itself remains highlighted in red despite being blank.

Kind regards,

Doug.

 

[Fluff]

Do you have any data in col A other than A1?