Conditional Formatting

mikeryan44

New Member
Joined
Sep 4, 2002
Messages
6
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.
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Mala

MrExcel MVP
Joined
Apr 4, 2002
Messages
627
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
 

Forum statistics

Threads
1,144,312
Messages
5,723,649
Members
422,508
Latest member
Lordkit1

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
Top