• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Conditional Formatting to hightlight Cells if contain text which is NOT a-z A-Z

Status
Not open for further replies.
A

andysw23

New Member
Hello,


I'm hoping someone can help me. I’m an Excel novice and I’ve been Googling for hours to try and find an answer for this problem. I need to apply conditional formatting to cells so they will highlight if the cell contains special characters. I’d like the cells to highlight if they contain anything which is not a normal character (a-z A-Z 0-9, space, full stop, comma etc) because with the special characters there are too many to mention.


So for example if a cell contained ‘B@rbara Windsor’ it would be highlighted because of the illegal @ symbol, but if it was correctly typed ‘Barbara Windsor’ it wouldn’t be highlighted.


I’m using Excel 2007


Please can you help?


Cheers.
 
Hi, andysw23!


I can think in two ways to perform it, one more complex and another simple.


The complex one implies using lots of auxiliary columns to validate each character, and the only advantage is that it would only use Excel formulas and no macros (VBA code).


The simple one involves just a short piece of text like this:

-----

[pre]
Code: 
Option Explicit

Function WrongText(psText As String) As Boolean
' constants
Const ksAllowed = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz 0123456789.,"
' declarations
Dim bOK As Boolean, iFound As Integer, I As Integer, J As Integer
' start
bOK = True
' process
For I = 1 To Len(psText)
iFound = 0
For J = 1 To Len(ksAllowed)
If Mid$(psText, I, 1) = Mid$(ksAllowed, J, 1) Then iFound = 1
Next J
If iFound = 0 Then
bOK = False
Exit For
End If
Next I
' end
WrongText = Not (bOK)
End Function
[/pre]
-----


Supossing your input data is in column A, in the conditional formatting formula you should type:

=WrongText(A1)

And apply to range $A:$A.


How to set up this?


1) VBA Code

a) from the Excel worksheet, Alt-F11, Insert, Module

b) in the right pane, copy the previous code


2) conditional formatting

a) from the Excel worksheet, Start tab, Styles group, Conditional Formatting, Admin Rules, New Rule, Use Formula (last option)

b) in the text box, copy the previous formula

c) click on format button, choose the highlight option wanted (for example, Fill, Yellow)

d) in the text box Apply To, type =$A:$A, click on Apply


That should be all.

If you want to add or delete frequently the allowed characters, you can move the allowed chars definition from within the code to a cell.

Just advise in this case, or if you need a sample sheet.


Regards!
 
Hi SirJB7,


Thank you so much for this and for the quick reply. I already have a user initiated macro in the sheet and I don't want to interfere with that (as I didn't write it) so using conditional formatting is the best option for me.


I tried what you suggested but I get an error message 'Enter a valid formula' when I try to click on 'OK' to save the new conditional format rule. I'm pasting the below into the formula section:


Option Explicit


Function WrongText(psText As String) As Boolean

' constants

Const ksAllowed = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz 0123456789.,"

' declarations

Dim bOK As Boolean, iFound As Integer, I As Integer, J As Integer

' start

bOK = True

' process

For I = 1 To Len(psText)

iFound = 0

For J = 1 To Len(ksAllowed)

If Mid$(psText, I, 1) = Mid$(ksAllowed, J, 1) Then iFound = 1

Next J

If iFound = 0 Then

bOK = False

Exit For

End If

Next I

' end

WrongText = Not (bOK)

End Function


Am I doing something wrong? You've kindly offered to send me a sample sheet. If you could do this I'd really appreciate it.


Many thanks.


Andy.
 
Hi, andysw23!

The code I sent you and that you typed here is only VBA code and cannot be pasted (if I understood correctly) in the formula section.

The two steps mentioned above were both for the same implementation: a formula that called an user defined function, and the statements of the UDF.

You can add a new module to contain just the code for this UDF, so as not to intefere with the existing code.

Here's the link:

http://www.2shared.com/file/-GDraj8p/Conditional_Formatting_to_high.html

If you wouldn't be able to handle the modifications in your workbook, you can upload it and I'll sent you back with the updates.

Regards!
 
Hi SirJB7,


Thanks for the sheet. I tried accessing this from home last night several times but I get an error that the download bandwidth is at capacity and to try again later. I tried all night and couldn't get the file. I'm back in the office now and my company has bloced that site so I can't download the file.


Would you be able to email it to me? My email address is andy hellwig at hotmail.co.uk (I added spaces to avoid the email address being picked up for SPAM).


Please let me know and sorry for being difficult.


Andy.
 
Hi Andy ,


In case you wish to do it using formulae , try the following :


Enter the list of valid characters as follows :


ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz 0123456789.,


in any unused cell , say G75.


Now , if you wish to validate cell F75 , have the following formula for conditionally formatting the cell :


=IFERROR(MIN(FIND(MID(F75,ROW(INDIRECT("1:"&LEN(F75))),1),G75)),0)=0


Depending on the text entered in cell F75 being valid or containing invalid characters , the cell should be highlighted.


Narayan
 
Hi NARAYANK991,


Thanks very much for this. This works perfectly for me in that cell. I changed the formula to match my sheet:


=IFERROR(MIN(FIND(MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1),A20)),0)=0


Now I have a new problem. I need to copy this to all the cells in the sheet (B5: AK10000). How do I do this? Can I just adjust the formula to something like this:


=IFERROR(MIN(FIND(MID(B5: AK10000,ROW(INDIRECT("1:"&LEN(B5: AK10000))),1),A20)),0)=0


I’m not familiar with the syntax and not sure if I need to add parenthesis or $ symbols to make this work.


Thanks for your time.


Andy.
 
Hi Andy ,


The list of valid characters , which you have entered in A20 , needs to be made an absolute reference , $A$20.


Regarding the other address references , you can try using the Format Painter , and copy the formats to the other cells , without changing the address references within the formula ; Excel will adjust them accordingly.


Narayan
 
Hi Narayan,


Thank you. That worked for me! :)


Thanks to you both for your help. This will save me hours of time.


Andy.
 
Hi, andysw23!

I'm just coming back and I see you've got your problem solved.

I'm emailing you the file, just in case.

Regards!
 
@NARAYANK991

Hi!

Very nice approach to solve the issue. I didn't get to solve it as simply as that. Chapeau, monsieur!

Regards!
 
Hi Narayan and SirJB7,


I have a similar challenge if you wish to accept? I'd now like to use the same principal as above, but highlight a cell using conditional formatting IF the cell does NOT contain specific codes.


Essentially one of the columns in my sheet is for a Country code, e.g. GB or US etc. If the user enters something which is not a recognised code (I can supply the list I'm working on), then the cell needs to highlight. For example if they enter US it's ok, but if they enter ZZ then it highlights.


I guess the solution is similar to the one above but because I'm looking for it to check for 2 digits together I don't know how to amend it.


I'll also be looking to do this in another column for the currency, e.g. GBP or USD. So same princinple again, but 3 characters in length.


Can you guys help with this one too?


Thanks in advance.


Andy.
 
Hi Andy ,


Since you will be checking for codes from a pre-defined list of codes , it is possible to use the MATCH function :


=IFERROR(MATCH(H13,A3:A12,0),0)=0


entered as the formula for Conditional Formatting , will allow H13 to be validated against the list of codes in the range A3:A12. Use RED as the colour , so that if the entered data does not match any of the pre-defined codes , it will flagged in RED.


The same principle can be applied to the currency codes.


Narayan
 
Hi Narayan,


Thanks for that. It does the trick... but only until row 65 where it then highlights valid data as invalid; the same data which is being passed in the rows above. My columns run to row 10,000 and I need the check to be on the whole column.


I'm not sure what I'm missing or doing wrong.


Andy.
 
Hi Andy ,


I had forgotten to mention that the range of codes ( A3:A12 in my example ) , should be absolute viz. $A$3:$A$12. I hope you have taken care of this.


With a simple MATCH function , I think the only reason for it to fail can be the input data itself ; can you copy + paste the cells where this CF is failing ? I can copy the same data and check why the formula fails.


Narayan
 
NARAYANK991 said:
Hi Andy ,


The list of valid characters , which you have entered in A20 , needs to be made an absolute reference , $A$20.


Regarding the other address references , you can try using the Format Painter , and copy the formats to the other cells , without changing the address references within the formula ; Excel will adjust them accordingly.


Narayan
Click to expand...



Hi Narayan,

From this formula, i need to maintain the foreign character. Do you know how can i include the code for foreign character?

Thanks,
 
Status
Not open for further replies.
Back
Top