Conditional Formatting VBA length and special characters

S

Serafin54

Board Regular
Joined
Apr 11, 2014
Messages
165
Office Version
  1. 2016
Platform
  1. Windows
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:
VBA 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
 
Sort by date Sort by votes
I appreciate those benefits of using Conditional Formatting, however, with the CF suggestions so far, isn't CF also a draw-back?

For example, if the CF formatting was set up with this list SpecialCharacter = Array("/", "&", ",", ".") and then data gets changed where say a "abc;d" appears in a cell, that data will not get highlighted because a semicolon was not included in the SpecialCharacter list. It goes back to my earlier point about the list needing to be a very long one if the CF is to be robust (though of course I know very little about what might or might not turn up in the "selection" data).

How are the values in the "selection" getting changed? Is it by formula, vba, manual entry etc?

Also, how are you deciding what to "select" before running the code? .. or is the selection made by the code at some point?
 
Upvote 0

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Peter_SSs said:
I appreciate those benefits of using Conditional Formatting, however, with the CF suggestions so far, isn't CF also a draw-back?

For example, if the CF formatting was set up with this list SpecialCharacter = Array("/", "&", ",", ".") and then data gets changed where say a "abc;d" appears in a cell, that data will not get highlighted because a semicolon was not included in the SpecialCharacter list. It goes back to my earlier point about the list needing to be a very long one if the CF is to be robust (though of course I know very little about what might or might not turn up in the "selection" data).

How are the values in the "selection" getting changed? Is it by formula, vba, manual entry etc?

Also, how are you deciding what to "select" before running the code? .. or is the selection made by the code at some point?
Click to expand...

We convert PDFs to Excel or get reports already in excel that have certain columns populated. The report will have certain columns that contain special characters or are longer than 30 character. We would manually need to update those cells to be import compliant.
 
Upvote 0
That didn't seem to address these questions?
Peter_SSs said:
How are the values in the "selection" getting changed? Is it by formula, vba, manual entry etc?

Also, how are you deciding what to "select" before running the code? .. or is the selection made by the code at some point?
Click to expand...

In any case, perhaps making the formatting into a Worksheet_Change event code may be a possibility but sticking with using Conditional Formatting for now, another consideration may be this.

The CF suggestions above, along with your original code create a CF rule for each character that is to be tested for so if the list of characters grows so does the number of CF rules.
Another option is to only have two rules: one for the >30 characters and one for the existence of special characters. Whilst my suggestion below keeps the number of rules to two, the rule for the special characters could itself become long. Anyway, I thought it worth suggesting and you can decide the best option for your circumstances.

I'm not sure if required or wanted but this code first removes and existing CF rules from the selected cells. If the list of characters is to include a double quote mark (") then special arrangements will need to be made for that. (Same with the other codes suggested)

VBA Code: 
Sub CFSpecial()
  Dim i As Long
  Dim s As String
  
  Const MyChars As String = "/$,.%$#@!&-%^*()+-=_\|"
  s = "count(" & Space(Len(MyChars)) & ")"
  For i = 1 To Len(MyChars)
    s = Replace(s, " ", ",find(""" & Mid(MyChars, i, 1) & """,rc)", 1, 1)
  Next i
  s = "=" & Replace(s, ",", "", 1, 1)
  With Selection
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlExpression, Formula1:="=len(rc)>30"
    .FormatConditions(1).Interior.Color = rgbYellow
    .FormatConditions.Add Type:=xlExpression, Formula1:=s
    With Selection.FormatConditions(2)
      .Font.Color = -16383844
      .Font.TintAndShade = 0
      .Interior.PatternColorIndex = xlAutomatic
      .Interior.Color = 13551615
      .Interior.TintAndShade = 0
    End With
  End With
End Sub
 
Upvote 0
Peter_SSs said:
That didn't seem to address these questions?


In any case, perhaps making the formatting into a Worksheet_Change event code may be a possibility but sticking with using Conditional Formatting for now, another consideration may be this.

The CF suggestions above, along with your original code create a CF rule for each character that is to be tested for so if the list of characters grows so does the number of CF rules.
Another option is to only have two rules: one for the >30 characters and one for the existence of special characters. Whilst my suggestion below keeps the number of rules to two, the rule for the special characters could itself become long. Anyway, I thought it worth suggesting and you can decide the best option for your circumstances.

I'm not sure if required or wanted but this code first removes and existing CF rules from the selected cells. If the list of characters is to include a double quote mark (") then special arrangements will need to be made for that. (Same with the other codes suggested)

VBA Code: 
Sub CFSpecial()
  Dim i As Long
  Dim s As String
 
  Const MyChars As String = "/$,.%$#@!&-%^*()+-=_\|"
  s = "count(" & Space(Len(MyChars)) & ")"
  For i = 1 To Len(MyChars)
    s = Replace(s, " ", ",find(""" & Mid(MyChars, i, 1) & """,rc)", 1, 1)
  Next i
  s = "=" & Replace(s, ",", "", 1, 1)
  With Selection
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlExpression, Formula1:="=len(rc)>30"
    .FormatConditions(1).Interior.Color = rgbYellow
    .FormatConditions.Add Type:=xlExpression, Formula1:=s
    With Selection.FormatConditions(2)
      .Font.Color = -16383844
      .Font.TintAndShade = 0
      .Interior.PatternColorIndex = xlAutomatic
      .Interior.Color = 13551615
      .Interior.TintAndShade = 0
    End With
  End With
End Sub
Click to expand...

Hi and thanks. Maybe I'm not understanding what you're looking for with the "how". The code is added as a button in the taskbar. When we get a report with the columns we know need to be checked, we click the column and click the button. The CF tells us if more than 30 or if contains special characters. We then can filter for the highlight, update accordingly based on the color by deleting the characters from the cells or shortening the string. We can't just have the code do it because we also have to map the proper coding to another sheet since more times than not, one is a main level and the other is on the worker level so we need to be sure
a) the shortening is approved in that we can't just automatically shorten a word. We need to try to figure out a readable abbreviation first, if not then try to make it as readable as possible.
b) we may need to replace the character with a word or abbreviation.

Not sure if this explains so let me know if not.
 
Upvote 0
I'm still not entirely clear but it doesn't matter.
Did you consider/try the single CF rule v the multiple CF rules?
 
Upvote 0
Peter_SSs said:
That didn't seem to address these questions?


In any case, perhaps making the formatting into a Worksheet_Change event code may be a possibility but sticking with using Conditional Formatting for now, another consideration may be this.

The CF suggestions above, along with your original code create a CF rule for each character that is to be tested for so if the list of characters grows so does the number of CF rules.
Another option is to only have two rules: one for the >30 characters and one for the existence of special characters. Whilst my suggestion below keeps the number of rules to two, the rule for the special characters could itself become long. Anyway, I thought it worth suggesting and you can decide the best option for your circumstances.

I'm not sure if required or wanted but this code first removes and existing CF rules from the selected cells. If the list of characters is to include a double quote mark (") then special arrangements will need to be made for that. (Same with the other codes suggested)

VBA Code: 
Sub CFSpecial()
  Dim i As Long
  Dim s As String
 
  Const MyChars As String = "/$,.%$#@!&-%^*()+-=_\|"
  s = "count(" & Space(Len(MyChars)) & ")"
  For i = 1 To Len(MyChars)
    s = Replace(s, " ", ",find(""" & Mid(MyChars, i, 1) & """,rc)", 1, 1)
  Next i
  s = "=" & Replace(s, ",", "", 1, 1)
  With Selection
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlExpression, Formula1:="=len(rc)>30"
    .FormatConditions(1).Interior.Color = rgbYellow
    .FormatConditions.Add Type:=xlExpression, Formula1:=s
    With Selection.FormatConditions(2)
      .Font.Color = -16383844
      .Font.TintAndShade = 0
      .Interior.PatternColorIndex = xlAutomatic
      .Interior.Color = 13551615
      .Interior.TintAndShade = 0
    End With
  End With
End Sub
Click to expand...

This works well also. The one thing i do like in the code that Johnny posted and which i really didn't consider is that it highlights in order of characters first then length which makes sense as the character may be the culprit causing the longer count.

While i do grasp the magnitude of having to do some babysitting on the index of special characters, it's a necessary evil i'm afraid. I was hoping there was a way to say if it's a cell that contains anything but "a-z","A-Z" or "0-9", highlight it but can't get to that.

Can you please elaborate on the single CF rule v the multiple CF rules?
 
Upvote 0
Last edited:
Upvote 0
Peter_SSs said:
Originally you did not want to highlight because of a space, now you do?
Click to expand...
No response to the 'space issue' yet so try this. It first applies the length > 30 rule and after that should highlight anything covered by the following, without having to list all the possible characters individually.
Serafin54 said:
I was hoping there was a way to say if it's a cell that contains anything but "a-z","A-Z" or "0-9", highlight it
Click to expand...
(At least it seems to work in my version)
VBA Code: 
Sub CFSpecial_v2()
   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))=0"
    With Selection.FormatConditions(2)
      .Font.Color = -16383844
      .Font.TintAndShade = 0
      .Interior.PatternColorIndex = xlAutomatic
      .Interior.Color = 13551615
      .Interior.TintAndShade = 0
    End With
  End With
End Sub
 
Upvote 0
Peter_SSs said:
I thought that I did in post #13





Originally you did not want to highlight because of a space, now you do?
Click to expand...

Sorry. missed the notification on this post.
For the CF single vs multiple what i guess i thought you meant was that there would be separate codes to be ran. I'm guessing i just misunderstood. As far as the space, no that was just an oversight that I left that out. Since the field might be an address, it would absolutely be a needed. My apologies.
 
Upvote 0
Serafin54 said:
For the CF single vs multiple what i guess i thought you meant was that there would be separate codes to be ran.
Click to expand...
No, I meant this (one rule for all special characters) ..

1656205315912.png


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

1656205156437.png


Serafin54 said:
As far as the space, no that was just an oversight that I left that out. Since the field might be an address, it would absolutely be a needed.
Click to expand...
So to highlight a cell that contains anything but "a-z","A-Z" or "0-9" or " " with just one CF rule, try this adaptation of my previous suggestion.

VBA 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)
      .Font.Color = -16383844
      .Font.TintAndShade = 0
      .Interior.PatternColorIndex = xlAutomatic
      .Interior.Color = 13551615
      .Interior.TintAndShade = 0
    End With
  End With
 
Upvote 0
You must log in or register to reply here.

Similar threads

Q
  • Solved
Using VBA instead of Conditional formatting
2
Replies
12
Views
869
Micron
M
S
  • Question Question
excel - conditional formatting - only for the last 50 rows of the tabl
Replies
3
Views
320
Vogateer
V
S
  • Question Question
Run VBA code from specific sheet till the end of workbook
Replies
7
Views
281
Alex Blakenburg
A
N
  • Question Question
My code is suddenly really slow
Replies
1
Views
375
RobP
R
K
  • Question Question
Variable losing set Range
Replies
8
Views
455
MARK858
MARK858

Forum statistics

Threads
1,224,820
Messages
6,181,159
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top