Delete all Conditional Formats but still retain all formats.

[Shazam]

I would like to delete all my Conditional Formats on my active worksheet BUT still retain all my formats. I found this code below but it does not work on large data. Is there way to have this code to be more efficient?


Sub FixConditionals()
Dim c As Range, i As Integer
For Each c In Selection 'select the range first, obviously
For i = 1 To c.FormatConditions.Count
c.Font.Color = c.FormatConditions(i).Font.Color
c.Interior.Color = c.FormatConditions(i).Interior.Color
c.Font.Bold = c.FormatConditions(i).Font.Bold
c.Font.Italic = c.FormatConditions(i).Font.Italic
c.FormatConditions(i).Delete
Next
Next
End Sub

[agarwaldvk]

Dear Shazam

If my understanding of your requirement is correct, why don't you just delete all the conditional format from the entire worksheet like so :-


Sub Test()
Cells.Select'This will select all the cells on the active worksheet or you first activate the specific sheet that you want to work on!
Selection.FormatConditions.Delete
End Sub


The above code will simply delete all your Conditional Formatting. Deleting Conditional Formatting does not delete Cell Formatting.


Best regards


Deepak

[Shazam]

Dear Shazam

If my understanding of your requirement is correct, why don't you just delete all the conditional format from the entire worksheet like so :-


Sub Test()
Cells.Select'This will select all the cells on the active worksheet or you first activate the specific sheet that you want to work on!
Selection.FormatConditions.Delete
End Sub

The above code will simply delete all your Conditional Formatting. Deleting Conditional Formatting does not delete Cell Formatting.


Best regards


Deepak

Hi Deepak,


I tried your code just to make sure if it deletes the format and it does. See i have 2000 rows of conditional formatting and when I scroll down it takes forever. So if there is a code that will delete all the conditional formatting and still retain the formats that will be great. Any ideas anyone?

[agarwaldvk]

Dear Shazam

I think there is come confusion here!

First, when you say "format" - we are referring to "Cell Formats" viz Bold, Italics of fonts, left/right alignment of text in the cells etc. - are we not?

In the code that I wrote for you, it doesn't matter, how many cells there are in your used range. It works at the worksheet level.

I have tried this on 5000 rows with different cells having differnt conditional formatting and cell formats.

For all of these, the conditional formatting was deleted but not the cell formatting.

I suggest you try that again without selecting any cells.

The code will do that for you - it will work on all the cells in the 'UsedRange'


Best regards


Deepak

[Shazam]

Hi Deepak,


Here is a sample of my workbook. Look in column A and the letters are bold because of the conditional formatting. Now when I run your code it will delete the conditional formats but it will not reatain the bolds in that column. Am I doing something wrong?

[agarwaldvk]

Dear Shazam

Mate, you seem to be a trifle confused here!

There is a difference between "Cell Formatting" that you do using :-

Format -> Cells -> and so on and

Conditional Formatting, which is the formatting that the cell assumes based on the value in the cell - depending upon what conditional formats you have used.

In your workbook, the boldness of the letters in column A is NOT because of cell formatting - it is because of conditional formatting.

When the code is run, the conditional formatting gets deleted. Hence, the letters in column A assume the cell formatting as it were. In your case, the cells are "Cell Formatted" to NOT BE BOLD. Hence, they are not BOLD.

I hope this helps!

If you need the boldness of the cell content, then format the cell content as bold. When you do that your conditional format should either not have the bold or should additionally have different colour coding for the text - otherwise, you would not know whether the boldness is because of conditional formatting or cell formatting.

Hope this clarifies the matter a bit.


Best regards


Deepak

[Bob Phillips]

Shaz,

See if this is quicker



Sub FixConditionals()
Dim c As Range, i As Integer
With Selection.Cells(1, 1)
For i = 1 To .FormatConditions.Count
.Font.Color = .FormatConditions(i).Font.Color
.Interior.Color = .FormatConditions(i).Interior.Color
.Font.Bold = .FormatConditions(i).Font.Bold
.Font.Italic = .FormatConditions(i).Font.Italic
.FormatConditions(i).Delete
Next
.Copy
End With
Selection.PasteSpecial Paste:=xlPasteFormats

End Sub

[unmarkedhelicopter]

Cross Post to Mr Excel :- http://www.mrexcel.com/forum/showthread.php?t=298287
If it's not, then it's damn close.

[RECrerar]

I don't think that's a cross post, same subject sure but it strikes me as a different person.

[unmarkedhelicopter]

I don't think that's a cross post, same subject sure but it strikes me as a different person.Maybe ... that's why I said :-

If it's not, then it's damn close.And why did you not post this as a reply to my post ???

[Shazam]

Cross Post to Mr Excel :- http://www.mrexcel.com/forum/showthread.php?t=298287
If it's not, then it's damn close.


unmarkedhelicopter,


I'am not Begie on mrexcel.com Please next time don't assume. Now if its the same name then I would understand you alerting everyone there might be cross posting going on here IF NOT contact one moderator from each forum and ask to look into if our email address are the same to confirm your suspicions, Because what you're doing really looks bad on me and might prohibits for anyone to help me in the near future. So unmarkedhelicopter don't be to hasty and take a little more patience to confirm your accusations.

Thanks!

Shazam!

[unmarkedhelicopter]

That's a little late to respond like that !
Your username is not the same, so who's to assume that your email address' will be the same ??? (How many people have more than 1 email address ? I have 10 that I currently use, others that are no longer valid or have fallen out of use).
I did say that :[QUOTE=unmarkedhelicopter]If it's not, then it's damn close.[QUOTE]By definition this, admits the possibility it's not. If that is the case you can say so (you have), but I would like to point out that you 'may' have found something useful from that thread.

I will NOT contact a moderator about something like that, the fact that it has been flagged should be all the notification that is required.
You have to admit that it is an extreme co-incidence that 2 threads be originated on the EXACT SAME SUBJECT within such a small time frame ?

I think I was justified in my post and I think you are only arguing about the wording, and given your response, syntax, sematics, context, grammar and spelling may be a lost art.

[Shazam]

That's a little late to respond like that !
Your username is not the same, so who's to assume that your email address' will be the same ??? (How many people have more than 1 email address ? I have 10 that I currently use, others that are no longer valid or have fallen out of use).
I did say that By definition this, admits the possibility it's not. If that is the case you can say so (you have), but I would like to point out that you 'may' have found something useful from that thread.

I will NOT contact a moderator about something like that, the fact that it has been flagged should be all the notification that is required.
You have to admit that it is an extreme co-incidence that 2 threads be originated on the EXACT SAME SUBJECT within such a small time frame ?

I think I was justified in my post and I think you are only arguing about the wording, and given your response, syntax, sematics, context, grammar and spelling may be a lost art.


I forgive you.:hi: