CODEWITHSUNDEEP
vbaexcelcharactercounting
echalmers
echalmers

Reputation: 1

Excel VBA - Build a function to highlight cells that exceed character limit

I have a column in an excel sheet that has a character limit. I want to be able to press an Active X Command button and have the function highlight all the cells that exceed the character limit. For example if the character limit is 3, the name Mark would be highlighted, but the name Joe would not be. Anyone know how to go about this?

This is what I have right now but it doesn't work. I'm brand new to this.

Private Sub CommandButton1_Click()


For i = 2 To 5
If Len(Cells(i, 1).Value) > 2 Then
Cells(1, 1).Interior.ColorIndex = 200
End If
Next i

End Sub

Upvotes: 0

Views: 2368

Answers (2)

Scott Holtzman
Scott Holtzman

Reputation: 27249

You can do this without VBA and just use Excel's built-in Conditional Formatting

  1. Select your range
  2. Click Alt + O, D to bring up Conditional Formatting Rules Manager enter image description here
  3. Click New Rule
  4. Select Use a formulato determine which cells to format
  5. Enter the formula =LEN(E4)>2 (formula based on example below) and click Format to fill the cell with the color of your choose.

enter image description here

  1. The end result will look like the screenshot below, except with your needed ranges.

enter image description here

Upvotes: 7

cardano dev 12
cardano dev 12

Reputation: 81

This works for me. I think the problem might be your line of code:

Cells(1, 1).Interior.ColorIndex = 200

Should be 

Cells(i, 1).Interior.ColorIndex = 200

See below:

For i = 2 To 5
If Len(Sheet1.Cells(i, 1).Value) > 2 Then
Sheet1.Cells(i, 1).Interior.ColorIndex = 37
End If
Next i

Upvotes: 1

Related Questions