Conditional Formatting

[mikeryan44]

We create pages of financial data and send them to another office for worldwide distribution. The pages sometimes contain junk data at the end of a line, like ">" and "[" should not.
I have created a spreadsheet with DDE links to the last two characters of each line on each page, which should always be blank. When we see a value pop in at the end of a line, we go into our pagebuilding processes and remove it there. (hopefully the developers will get this fixed soon!)
My question in the meantime is this: Is there a way for Conditional Formatting to be set up so the cells go red when foreign characters come in?
This would make it lot easier to notice when this happens.

 

[Mala]

Try the following:

a. In a cell type all the characters that you wish the conditional formatting to trap - e.g. type '{}[]<>' if you wish to just trap these six characters.

b. Name this cell 'BadStr'

c. In a code module insert the following function:

Function BadFound(Ref) As Boolean
Txt = Ref
BadStr = Range("BadStr")
L = Len(BadStr)
BadFound = False
For i = 1 To L
BadFind = Mid(BadStr, i, 1)
Pos = InStr(1, Txt, BadFind)
If Pos > 0 Then BadFound = True: Exit For
Next i
End Function

d. Select the range to which the conditional formatting is to be applied. Let us say this range is B3:B100 - select the range so that B3 is the active cell (this is important as you will see in item e below)

e. Do Format>Conditional formatting and in the dialog box select 'Formula is' and in the text box type '=(BadFound(B3)=True)' - just make sure that the address you enter is the address of the active cell i.e. the address that appears in the left part of the formula bar.

f. Specify the conditional format of your choice in terms of patterns, font colors etc.

g. Click OK to dismiss the dialog box.

If you need to change BadStr (the characters to trap) just edit the contents of the cell named 'BadStr' and the conditional formatting would then automatically refer to the modified BadStr.

Hope this helps...

_________________
Mala Singh
MrExcel Graphics & Engineering Division
This message was edited by Mala on 2002-10-11 08:13

 

[royUK]

Not sure whether this will help but the asap addin (free from http://www.asap-utilities.com) has a function to remove special characters. It's worth downloading anyway