Conditional Formatting VBA length and special characters

S

Serafin54

Board Regular
Joined
Apr 11, 2014
Messages
155
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

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
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

N
  • Question
My code is suddenly really slow
Replies
1
Views
129
RobP
R
P
  • Solved
Loop Not Looping too Next Sheet
Replies
3
Views
234
Joe4
Joe4
W
  • Solved
VBA Apply Formatting Based on 2 Criteria and If Another Cell Contains...
2
Replies
13
Views
213
Joe4
Joe4
tezza
  • Question
VBA conditional order issue
Replies
6
Views
443
kevin9999
K
MustacheKaboom
  • Solved
VBA Noob. All but the last step works... GAH!!
Replies
4
Views
326
MustacheKaboom
MustacheKaboom

Forum statistics

Threads
1,214,589
Messages
6,120,416
Members
448,960
Latest member
AKSMITH

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