Conditional Formatting VBA length and special characters

[Serafin54]

Hi!

I have a code that conditionally formats any cell in a selected column yellow if it contains more than 30 characters.

I also have another code embedded that i recorded a macro for to find and highlight special characters such as "-", "&", "." etc. and highlight those in red. This code seems super bloated and adding on is growing the code longer and longer. Is there a more condensed way to highlight cells that don't contain only 0-9, a-z or space so i don't have to babysit the code?

Thanks.

Length code:

Sub Macro3()
'Highlights cells >30 characters in yellow
With Selection
      .FormatConditions.Add Type:=xlExpression, Formula1:="=LEN(rc)>30"
      .FormatConditions(.FormatConditions.Count).Interior.Color = rgbYellow

   End With
    Selection.FormatConditions.Add Type:=xlTextString, String:="/", _
        TextOperator:=xlContains
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .Color = -16383844
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 13551615
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Selection.FormatConditions.Add Type:=xlTextString, String:="&", _
        TextOperator:=xlContains
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .Color = -16383844
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 13551615
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    ActiveWindow.SmallScroll Down:=21
    Selection.FormatConditions.Add Type:=xlTextString, String:=",", _
        TextOperator:=xlContains
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .Color = -16383844
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 13551615
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Selection.FormatConditions.Add Type:=xlTextString, String:=".", _
        TextOperator:=xlContains
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .Color = -16383844
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 13551615
        .TintAndShade = 0
End Sub

 

[GWteB]

One way would be to introduce a separate procedure which can be invoked as many times as you like, each time with the character that needs to be part of the condition.
See if this works for you.

Public Sub Serafin54()
   
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=LEN(rc)>30"
    Selection.FormatConditions(Selection.FormatConditions.Count).Interior.Color = rgbYellow
   
    SetFC "/"
    SetFC "$"
    SetFC ","
    SetFC "."
    SetFC "&"
End Sub


Public Sub SetFC(ByVal argChar As String)

    Dim fc As Excel.FormatCondition
    Set fc = Selection.FormatConditions.Add(Type:=xlTextString, String:=argChar, TextOperator:=xlContains)
    With fc
        .Font.Color = -16383844
        .Font.TintAndShade = 0
        .Interior.PatternColorIndex = xlAutomatic
        .Interior.Color = 13551615
        .Interior.TintAndShade = 0
    End With
End Sub

EDIT: corrected an omission which would produce a run-time error ...

 

[GWteB]

.... a more elegant way would propably be:

Public Sub Serafin54_v2()
    
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=LEN(rc)>30"
    Selection.FormatConditions(Selection.FormatConditions.Count).Interior.Color = rgbYellow
    
    SetFC "/$,.%$#@!"
End Sub


Public Sub SetFC(ByVal argChars As String)

    Dim i As Long
    For i = 1 To VBA.Len(argChars)
    
        Dim Char As String
        Char = VBA.Mid$(argChars, i, 1)
        
        Dim fc As Excel.FormatCondition
        Set fc = Selection.FormatConditions.Add(Type:=xlTextString, String:=Char, TextOperator:=xlContains)
        With fc
            .Font.Color = -16383844
            .Font.TintAndShade = 0
            .Interior.PatternColorIndex = xlAutomatic
            .Interior.Color = 13551615
            .Interior.TintAndShade = 0
        End With
    Next i
End Sub

 

[johnnyL]

Similar approach I came up with:

Sub Macro3()
'
'Highlights cells >30 characters in yellow
'
    With Selection
        .FormatConditions.Add Type:=xlExpression, Formula1:="=LEN(rc)>30"
        .FormatConditions(.FormatConditions.Count).Interior.Color = rgbYellow
    End With
'-------------------------------------------------------------------------------------------
    Dim i                   As Long
    Dim SpecialCharacter    As Variant
'
    SpecialCharacter = Array("/", "&", ",", ".")                                                     ' 1D zero based array
'
    For i = 0 To UBound(SpecialCharacter)
        Selection.FormatConditions(1).StopIfTrue = False
        Selection.FormatConditions.Add Type:=xlTextString, String:=SpecialCharacter(i), TextOperator:=xlContains
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
'
        With Selection.FormatConditions(1)
            .Font.Color = -16383844
            .Font.TintAndShade = 0
            .Interior.PatternColorIndex = xlAutomatic
            .Interior.Color = 13551615
            .Interior.TintAndShade = 0
        End With
    Next
'-------------------------------------------------------------------------------------------
End Sub

 

[Serafin54]

Man, all codes work well. #3 and #4 especially. I do appreciate the help on this. I'm going to test it out through the day tomorrow to see which works best in my situation. Thank you very much!

 

[GWteB]

You are welcome and thanks for letting us know.

 

[johnnyL]

Man, all codes work well. #3 and #4 especially.

In case you didn't realize, both of those posts that you mentioned are completely editable to expand/reduce what you are needing.

Post #3

    SetFC "/$,.%$#@!"
End

Post #4

    SpecialCharacter = Array("/", "&", ",", ".")                                                     ' 1D zero based array

 

[Peter_SSs]

Does it need to be Conditional Formatting applied by the code? Could the code just do the formatting itself?

highlight cells that don't contain only 0-9, a-z or space

Given that there is a long list of characters other than those you mention above, this code could highlight cells with any non-complying characters without having to actually list all those possible characters.

If you wanted to test it then remove any existing conditional formatting from "the selection" before running the code.
If you intended that upper case letters are also acceptable then uncomment the green line of code.

Sub HighlightSpecial()
  Dim RX As Object
  Dim c As Range
  
  Set RX = CreateObject("VBScript.RegExp")
'  RX.IgnoreCase = True
  RX.Pattern = "[^a-z0-9 ]"
  With Selection
    .Font.Color = 0
    .Interior.ColorIndex = 0
    For Each c In .Cells
      If Len(c.Value) > 30 Then
        c.Interior.Color = rgbYellow
      ElseIf RX.test(c.Value) Then
        c.Font.Color = -16383844
        c.Interior.Color = 13551615
      End If
    Next c
  End With
End Sub

 

[Serafin54]

question though. If i wanted to create an input box to ask the user if they want to just find len only or both len and characters, is there a good way to tackle that by adding a few lines or

In case you didn't realize, both of those posts that you mentioned are completely editable to expand/reduce what you are needing.

Post #3

    SetFC "/$,.%$#@!"
End

Post #4

    SpecialCharacter = Array("/", "&", ",", ".")                                                     ' 1D zero based array

Thanks. I definitely caught that and had to add a hyphen. So far they are both doing the job well.

 

[Serafin54]

Does it need to be Conditional Formatting applied by the code? Could the code just do the formatting itself?


Given that there is a long list of characters other than those you mention above, this code could highlight cells with any non-complying characters without having to actually list all those possible characters.

If you wanted to test it then remove any existing conditional formatting from "the selection" before running the code.
If you intended that upper case letters are also acceptable then uncomment the green line of code.

Sub HighlightSpecial()
  Dim RX As Object
  Dim c As Range
 
  Set RX = CreateObject("VBScript.RegExp")
'  RX.IgnoreCase = True
  RX.Pattern = "[^a-z0-9 ]"
  With Selection
    .Font.Color = 0
    .Interior.ColorIndex = 0
    For Each c In .Cells
      If Len(c.Value) > 30 Then
        c.Interior.Color = rgbYellow
      ElseIf RX.test(c.Value) Then
        c.Font.Color = -16383844
        c.Interior.Color = 13551615
      End If
    Next c
  End With
End Sub

The part I really like about the conditional formatting is once you address the issue, the criteria is no longer met so the cell highlight is cleared. When we have a list of 100 + cells, it helps with keeping track of where you've been. And sometimes a name will have special characters and be more than 30 characters. When you address one in length but still have the special characters, or vice versa, the color changes to let you know only part of the issue has been resolved.