Clear Format problem

I am trying to write a macro to clear ALL format in a range of cell. However, 
I run into a problem. 

I am using the Selection.ClearFormats command and it works in most cases, 
but fails when multiple format are applied to the cell. 

For example if a cell only contains "Hello wonderful world" all in bold than 
ClearFormats will remove the bold formatting. But if the cell contains
"Hello wonderful world" where only "wonderful" is in bold,  than 
ClearFormats does not work and the word "wonderful" remains in bold.

Does anyone know how to clear format all the time.

Thanks
0
Utf
12/11/2009 1:04:01 AM
excel.programming 6508 articles. 2 followers. Follow

2 Replies
1274 Views

Similar Articles

[PageSpeed] 48

You could do something like this:

for each cell in selection
    if not isempty(cell) then cell.value = cell.value
next

Robert Flanagan
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

"The Howler" <TheHowler@discussions.microsoft.com> wrote in message 
news:3356260F-2E03-48D3-8EC0-4BC5B4DBDE36@microsoft.com...
>I am trying to write a macro to clear ALL format in a range of cell. 
>However,
> I run into a problem.
>
> I am using the Selection.ClearFormats command and it works in most cases,
> but fails when multiple format are applied to the cell.
>
> For example if a cell only contains "Hello wonderful world" all in bold 
> than
> ClearFormats will remove the bold formatting. But if the cell contains
> "Hello wonderful world" where only "wonderful" is in bold,  than
> ClearFormats does not work and the word "wonderful" remains in bold.
>
> Does anyone know how to clear format all the time.
>
> Thanks 


0
Robert
12/11/2009 3:16:37 AM
Careful with this one... if any of the cells have formulas in them, those 
formulas will disappear. Perhaps this test would be better...

If Not cell.HasFormula Then If Len(cell.Value) Then cell.Value = cell.Value

-- 
Rick (MVP - Excel)


"Robert Flanagan" <nospam@nospam.net> wrote in message 
news:uJnsbBheKHA.4952@TK2MSFTNGP06.phx.gbl...
> You could do something like this:
>
> for each cell in selection
>    if not isempty(cell) then cell.value = cell.value
> next
>
> Robert Flanagan
> http://www.add-ins.com
> Productivity add-ins and downloadable books on VB macros for Excel
>
> "The Howler" <TheHowler@discussions.microsoft.com> wrote in message 
> news:3356260F-2E03-48D3-8EC0-4BC5B4DBDE36@microsoft.com...
>>I am trying to write a macro to clear ALL format in a range of cell. 
>>However,
>> I run into a problem.
>>
>> I am using the Selection.ClearFormats command and it works in most cases,
>> but fails when multiple format are applied to the cell.
>>
>> For example if a cell only contains "Hello wonderful world" all in bold 
>> than
>> ClearFormats will remove the bold formatting. But if the cell contains
>> "Hello wonderful world" where only "wonderful" is in bold,  than
>> ClearFormats does not work and the word "wonderful" remains in bold.
>>
>> Does anyone know how to clear format all the time.
>>
>> Thanks
>
> 

0
Rick
12/11/2009 3:29:49 AM
Reply:

Similar Artilces:

background image formatting
Hi All, How can I make a background image stretched instead of tiled? I am using a default picture in publisher of clouds and they of course don't look right being tiled. Thanks a lot David Resize the image? --- If you're asking about web bg's, you can but the result is u g l y -- Rob Giordano Microsoft MVP - FrontPage "David" <David@discussions.microsoft.com> wrote in message news:9E6E0F22-9511-4EEC-963B-0B359D511FA2@microsoft.com... | Hi All, | How can I make a background image stretched instead of tiled? | I am using a default picture in publisher ...

saving format
i have a spreadsheet that i backup & copy to a new folder but the cell formulas are replaced with the cell values. how do i keep the formula format. thanks Save two copies??? One with the formulas and one after the formulas have been converted to values. bob wrote: > > i have a spreadsheet that i backup & copy to a new folder but the cell > formulas are replaced with the cell values. how do i keep the formula > format. > > thanks -- Dave Peterson ec35720@msn.com Or are you saying that when you make the backup copy the formulas are somehow changed to values...

Preserving Cell Formats in Excel Query
I am doing queries on a large workbook of multiple Excel spreadsheets. When I query the data, the original data formats don't carry through to the query results. Is there a way to carry original formatting through to Excel Query results Any insight would be appreciated Karen S No, you can import the data, but not the formats. If you're importing programmatically, you could apply the formatting as part of the import procedure. Karen S wrote: > I am doing queries on a large workbook of multiple Excel spreadsheets. When I query the data, the original data formats don't car...

New Problem
In 2007: In a form I created a button, used the wizard to set it to go to next record when clicked. When I click it, I get this: The expression ON CLICK you entered as the event property setting produced the following error: Ambiguous name detected: PRINT_CLICK. Now, if I wrote the VBA/Macro, I could understand it not working if I made a mistake, but this button was generated by ACCESS and doesn't work. Same problem with Previous record button and Save Record button. Thanks in advance, Vito On 13 May 2010 16:00:59 GMT, Vito wrote: > In 2007: > > In ...

Format cells with dates
Is there a way to format cells so that dates would change when the lead date is changed. for example, when I input monday's date, tue, wed, thur, etc will follow suit. Pat, Assuming the first date is in A1 B1: =A1+1 C1: =B 1+1 etc. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Pat" <anonymous@discussions.microsoft.com> wrote in message news:115681B0-348E-447E-BB1F-F8347CFDB19B@microsoft.com... > Is there a way to format cells so that dates would change when the lead date is...

Authentication mode
Hi: Currently I have installed the MS CRM 1.2 Server on a server that host another application(sharepoint portal server & project server), this application requires SQL Authentication for work,but CRM requieres Windows Authentication Mode; at least the CRM implementacion guide document recommends this type of authentication, is there any way for work with both applications on same server ?. Microsoft CRM can work with SQL in mix mode authentication. Frank Lee Workopia, Inc. http://www.workopia.com/Links.htm San Francisco, CA "Rafael Diaz" wrote: > Hi: > >...

Problems with Access 2000 reports in Access 2003
Hi, we have a sent out a 2000 format access database to a client running Access 2003. When trying to open any of the reports they get an error - "the openreport action was cancelled" - Not sure if they get this by running the report from the database window or if it's only when done through a form (buttons that run docmd.openreport when pressed). Is anyone aware of any issues that may be causing this? I couldn't find anything elsewhere on the MS site. Thanks - Angie P.S. the reports work fine in 2000 and XP. ...

URGENT!!! Problem with row data being truncated in a copy worksheet sub #2
Dave, Thanks for responding. I tried this but I could not get it t work in conjunction with the entire module. It dies right afte copying and PasteSpecial Values It does not kill the temp file or loa the newly created sheet into an e-mail. Any Ideas -- Doctor ----------------------------------------------------------------------- DoctorV's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=514 View this thread: http://www.excelforum.com/showthread.php?threadid=26863 You may want to post your current procedure. DoctorV wrote: > > Dave, Thanks for respon...

Conditional Formating Help
Hi I wonder if anyone could help me, I am after code for the following. cell e6 = Keys Sent Column f6 = Keys due Column g6 = Keys received What I want the script to do is if there is no value in g6 and todays date is greater then the date stated in F6, then the cell turns red (prompt to inform me that keys are late). Many thanks Dan Use a CF formula of =AND(G6="",TODAY()>F6) HTH Bob "housinglad" <housinglad@discussions.microsoft.com> wrote in message news:A5887369-33DA-489A-BEC9-8173707313C6@microsoft.com... > Hi > ...

Conditional Formating Furmula
I have to enter a 14 digit licence number in a field this is a mix of numbers and leters. can some one please give me a formula that i can use in conditional formating tol show if there are to few or to many digits. (Using excel 2003) Thaks in advance. CF/ Formula is/ =LEN(A1)<14 Format as desired for too few CF/ Formula is/ =LEN(A1)>14 Format as desired for too many Rather than using CF you could, of course, use Data validation to require LEN(A1) to be 14. -- David Biddulph "jockj215" <jockj215@discussions.microsoft.com> wrote in message news:A24...

Conditional Formatting
Hi All, I need help on conditional Formatting. I have a column of data with future DATE, such as 2 Jan 09, 4 Des 09, etc I would like to assign automatically different colors to those data that match these condition: If (TODAY's date < Data's date) And more than 30 days, COLOUR is Orange If (TODAY's date < Data's date) And more than 60 days, COLOUR is Yellow If (TODAY's date < Data's date) And more than 90 days, COLOUR is Green If (TODAY's date >= Data's date) And more than 30 days, COLOUR is Red I would like to gave it AUTO...

OFFSET problems in dynamic range
Hi, I'm having the strangest problem with the OFFSET function. I have 5 dynamic ranges in my worksheet. Aimline =OFFSET('Weekly ORF'!$F$4,1,0,COUNTA('Weekly ORF'$F:$F)-1,1) Sessions =OFFSET ('Weekly ORF'!$D$4,1,0,COUNTA('Weekly ORF'$E:$E)-1,1) WCPM =OFFSET ('Weekly ORF'!$G$4,1,0,COUNTA('Weekly ORF'$G:$G)-1,1) Date =OFFSET ('Weekly ORF'!Aimline,0,-1) Date2 =OFFSET ('Weekly ORF'!WCPM,0,-1) The problem is that the two names for Date and Date2 keep reverting to =OFFSET ('Excel Template.xls'!Aimline,0,-1) and =OFFSET (&#...

OnGetMinMaxInfo problem
Hi, I added an OnMinMaxInfo() function to my dialog so i can set the minimum tracking width and height for my dialog, I added this prototype to my dialog class: afx_msg void OnGetMinMaxInfo(MINMAXINFO* lpMMI); Then I added this function to set the min and max values: void CMessDlg::OnGetMinMaxInfo(MINMAXINFO* lpMMI) { POINT min; min.x = 200; min.y = 200; lpMMI->ptMinTrackSize = min; CDialog::OnGetMinMaxInfo(lpMMI); } It compiles but i can still resize the dialog to a smaller width/height, Any help would be apreciated :) Thx in advance, Thomas "Thomas" <Da_QuiK@fake...

Urgent Question Please Help: Credit Card Setup Problem
We have an urgent question regarding credit card setup/cash receipts/bank rec We understand that in Great Plains when setting up a Credit Card to be used by a Customer, it must be setup as a "Bank Card", not a "Charge Card", in order for the transaction to hit Bank Rec. However, we mistakenly setup all the Credit Cards as "Charge Cards", therefore after posting Credit Card Cash Receipt transactions in Receivables, the transaction does not appear in Bank Rec for us to perform Bank Deposits. Please Please Help!!! Is there any way to "fix" those pos...

Problem running MS CRM 3.0 over SSL (https)
I have setup an install of Microsoft CRM 3.0 on a high spec Windows 2003 Small Business Server and it all worked happily. I then generated an SSL certificate and applied it to the site in IIS. Initially when you load CRM (using the web client) it loads the left hand menu and navigation panes and shows the top menus and the user which is logged in however to main content area remains blank. After a time out period the error "An error has occurred. For more information, contact your system administrator" appears. The strange thing is you can navigate the menus etc and even edit...

weird formatting
Hi all, Anyone ever seen this before? This was what happened to a mac excel spreadsheet the user doesnt know how she did this but i wondered if anyone might recognize this current format. In hopes we can change it back. Formatting is below. thx for any help ... Ed 2oC//9yIAGAf0L//2tC//9qQAAg= </data> <data> AG0AFgAAPz8/Pz8/Pz8BcF9AAAAA JQUubXBlZwAADHZpZGVvL3gtbXBl Zw9NUEVHIG1lZGlhIGZpbGVtc2Z0 AAAAMr//AAIRUXVpY2tUaW1lIFBs dWdpbgDagL//3IgAYB/Qv//a0L// 2pAACA== </data> <data> AGwAFgAAPz8/Pz8/Pz8B...

WindowsCE problems
I have some problems building application for Pocket PC (MDA1), maybe someone could help me. I have these two problems: -I need to create modal dialog box, but after creation sometimes I need to access it (in response to system calls) and show some messages... what is the best way to access modal dialog box while it's running? -is there a way to change colors of single items in list-view control ? Currently, when I'm using SetTextColor before each InsertItem, color changes but for all items in list-view (CListCtrl) :\ Is there way make this work? I'm using Microsoft eMbedded Visu...

Draft Folder problem #2
I'm running outlook 2000. My draft folder is called draft1. Can't seem to rename it! There isn't a folder named draft so what's happening? Isn't a big problem, but I should be able to solve it and I can't. Help. Thanks in advance Peter You could try to start Outlook from the run line with the /resetfoldernames switch. Start-->Run outlook.exe /resetfoldernames Reference: http://office.microsoft.com/en-gb/assistance/HP010031101033.aspx "Peter Brown" <peter.jennifer@gmail.com> wrote in message news:TErag.449$TF.2645@news-1.opaltelecom.net... &...

Formatting hyperlinks in an Excel cell 02-16-10
Two of the columns in a spreadsheet (Excel 2003) that I use record email and web addresses. All of them appear as hyperlinks i.e. blue and underlined but some occasionally seem to lose their hyperlink properties. This means that when one hovers over them, the cursor stays as the usual Excel cross rather than changing to the hand/finger symbol. Also, clicking on the former does not launch the browser. Is there any way to ensure they are formatted, and work, as hyperlinks please? TIA V ...

Formatting date fields after export
I am experiencing problems with my exported date fields into Excel from other applications. The data formats to "yyyy-mm-dd" and cannot be modified unless I double-click on each field. Has anyone else experienced this problem? And what solutions would you suggest? It is probably seen as text, select the imported dates, do data>text to columns, click next twice, under column data format select date and YMD click finish Regards, Peo Sjoblom "Raymond" wrote: > I am experiencing problems with my exported date fields into Excel from other > applications. The d...

Problem of ghost unread posts?
Hi All! I'm using Outlook Express. Three times today I'm seeing unread posts reported that can't be found. Synchronizing doesn't cure the problem. When I use Edit > Mark All Read I get an OE hang up. Ctrl + Alt + Del > End Task Return to News and the ghost has gone. Anyone else? Cause? Solution? Not critical but annoying. I wonder if it's a remains of a spam that's been removed before I read it. -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au Excel and Word Function Lists (Classifications, Syntax and Arguments) available fr...

Problem Sending Mail w/Outlook 2002
I receive and send a lot of email using Outlook 2002 and my Gmail account with no problem. However, I have two friends to whom I send emails that are always returned. I get an error message like this: Technical details of permanent failure: PERM_FAILURE: SMTP Error (state 9): 550 Invalid recipient: However, if I send the emails to them directly from Gmail, they go through just fine. Can anyone explain why they fail with Outlook? Pat This sounds like an issue with the SMTP server you have setup in outlook - not outlook itself. "patetc" <PatETC@gmail.com> wrote in messag...

Software Distribution
Hi, I try to install software using msi with mst. Software supplier told that I must run msi two times. First for some options and secound for another ( language option ). I try solve this with two way: 1) Add two mst to one package. I prepare mst with ORCA. After add secound mst and click OK i got error: "An error occurred accessing the software installation data in the active directory" and event log: Event id 110: Software Installation was unable to generate the script for \\dc\client.msi. The following error was encountered: Error applying transforms. Veri...

CSV formatted text file to Excel
Hi all, I am writing a small VC++ application of how to import the CSV formatted ..txt file to excel. I am facing problem while parsing the text file. "TicketNo","CarNo","PersonAge" 12534 , 763534 , 23 12345 , 624333, 24 The problem is in MFC there is a SetValue2(CoeVariant:column data) method in which if i will pass an array(12534) then it will be imported to excel.For example IfI will search for the "employee number" field in text file then the values passed to SetValue2()should be 12543 and 12345.But Using C++ I cannot do so as I...

Problems saving HKLM
Hi there, I am trying to save the reg keys into a file: HKEY_CLASSES_ROOT HKEY_CURRENT_CONFIG HKEY_CURRENT_USER HKEY_LOCAL_MACHINE HKEY_USERS My program has both "SeBackupPrivilege" and "SeRestorePrivilege". I am using the RegSaveKey API. The program succesfully stores these: HKEY_CLASSES_ROOT HKEY_CURRENT_CONFIG HKEY_CURRENT_USER The machine does not store these: HKEY_LOCAL_MACHINE HKEY_USERS Error ("Access is denied"). Can someone tell me how to save the HKLM amd HKU? This probably what the problem is: http://msdn.microsoft.com/library/default.asp?ur...