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

 

[Serafin54]

No, I meant this (one rule for all special characters) ..

.. rather than this (multiple rules, 1 for each special character).

Ok Thank you. This now makes perfect sense.

This code does work very very well and without the worry and concern of needing to babysit it should a latin accent show up in a file for instance (which i tested and it found it).

Is there a better way to set the priority of the CF than what i added to your code below? It seems to work but not sure if it in any way messes with the integrity of your original code.

Sub CFSpecial_v3()
   With Selection
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlExpression, Formula1:="=len(rc)>30"
    .FormatConditions(1).Interior.Color = rgbYellow
    .FormatConditions.Add Type:=xlExpression, Formula1:= _
      "=MIN((ABS(77.5-CODE(MID(UPPER(rc),ROW(INDIRECT(""1:""& LEN(rc))),1)))<13)" _
      & "+(ABS(52.5-CODE(MID(rc,ROW(INDIRECT(""1:""& LEN(rc))),1)))<5)" _
      & "+(MID(rc,ROW(INDIRECT(""1:""& LEN(rc))),1)="" ""))=0"
    With Selection.FormatConditions(2)
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority  
      .Font.Color = -16383844
      .Font.TintAndShade = 0
      .Interior.PatternColorIndex = xlAutomatic
      .Interior.Color = 13551615
      .Interior.TintAndShade = 0
    End With
  End With
End Sub

 

[Peter_SSs]

Is there a better way to set the priority of the CF than what i added to your code below?

I would probably just apply the two conditions in the opposite order in the first place.

Sub CFSpecial_v4()
  With Selection
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlExpression, Formula1:= _
      "=MIN((ABS(77.5-CODE(MID(UPPER(rc),ROW(INDIRECT(""1:""& LEN(rc))),1)))<13)" _
      & "+(ABS(52.5-CODE(MID(rc,ROW(INDIRECT(""1:""& LEN(rc))),1)))<5)" _
      & "+(MID(rc,ROW(INDIRECT(""1:""& LEN(rc))),1)="" ""))=0"
    With Selection.FormatConditions(1)
      .Font.Color = -16383844
      .Font.TintAndShade = 0
      .Interior.PatternColorIndex = xlAutomatic
      .Interior.Color = 13551615
      .Interior.TintAndShade = 0
    End With
    .FormatConditions.Add Type:=xlExpression, Formula1:="=len(rc)>30"
    .FormatConditions(2).Interior.Color = rgbYellow
  End With
End Sub

 

[Serafin54]

I would probably just apply the two conditions in the opposite order in the first place.

Sub CFSpecial_v4()
  With Selection
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlExpression, Formula1:= _
      "=MIN((ABS(77.5-CODE(MID(UPPER(rc),ROW(INDIRECT(""1:""& LEN(rc))),1)))<13)" _
      & "+(ABS(52.5-CODE(MID(rc,ROW(INDIRECT(""1:""& LEN(rc))),1)))<5)" _
      & "+(MID(rc,ROW(INDIRECT(""1:""& LEN(rc))),1)="" ""))=0"
    With Selection.FormatConditions(1)
      .Font.Color = -16383844
      .Font.TintAndShade = 0
      .Interior.PatternColorIndex = xlAutomatic
      .Interior.Color = 13551615
      .Interior.TintAndShade = 0
    End With
    .FormatConditions.Add Type:=xlExpression, Formula1:="=len(rc)>30"
    .FormatConditions(2).Interior.Color = rgbYellow
  End With
End Sub

I tried that and couldn't get it to format correctly for the life of me. I see the issue was the 3rd to last row ".FormatConditions(2). I left it as (1). Thank you! This is perfect. Thank you!

 

[Peter_SSs]

You're welcome.