Remove conditional formatting but keep format

[danesrood]

Dear All

I would like to be able to delete all of the the conditions but keep the formatting that was generated by the CF in the first place.

If this can be done it would be nice to be able to do it either for the active sheet or a selected range.

Fingers crossed.

[Kenneth Hobs]

Reading conditional formats and their conditions will vary depending on your Excel version. So, tell us your Excel version first.

[danesrood]

Kenneth

Thank you for your reply.

I think its 2003 - I do know that its not 2007.

[mikerickson]

I have a sub for that on my other computer. Be back in 4 hr.

[mikerickson]

This should do it for Excel 2004 and earlier. For later some tweeks need to be added.

[danesrood]

Mike

Thank you so much for this.

The code works fine in the CF workbook but doesn't appear be working elsewhere.

As it won't work across workbooks I have copied the code from the example file into the appropriate workbook but nothing seems to be happening after I have selected the range and pressed ok, have I missed something fundemental?

These are the three conditions that I have in one of the selected cells:

Condition 1 Formula Is =AND(B7>=C7*1.2,C7>0) Format = Black Text on a Pastel Red background

Condition 2 Formula Is =AND(B7>=C7*0.8,B7<=C7,C7>0) Format = Black Text on a Pastel Green background

Condition 3 Cell Value Is Greater than =C7 Format = Black Text on a Pastel Amber background

[mikerickson]

If I have the code in Workbook1.xls, run the macro and select a sheet from another (open) workbook, it works for me.

[danesrood]

Mike

Sorry for long delay in coming back.

I have your CF workbook open along with another one, that has the workbook and sheets unprotected, yet when I press the button it will not allow me to select anything from the 2nd workbook.

Strange

According to the Help/About option I am using Excel 2002 (10.6866)

[mikerickson]

In a new workbook, put this in a normal module. Open a couple of other workbooks and see if you can change books while the dialog is asking you for a cell
Sub test()
MsgBox Application.InputBox("select a cell", Type:=8).Address(, , , True)
End Sub

[danesrood]

Mike

I have now had a chance to run the new line of code that you suggested in a new workbook and module and as before I am unable to move across to another open workbook to select some cells.

[mikerickson]

Is the second workbook opened in the same instance of Excel?

On those few occasions that I work on a Windows machine, I sometimes inadvertently open a new instace of Excel when my goal is to open another workbook in the same instance.

If I remember correctly, clicking on a file icon opens a new instance of Excel. Using the Open command from within an instance does not open another. (Windows users, please correct me if I'm wrong.)

[BrianMH]

Opening a file from an icon doesn't open a new instance but opening excel from the start menu and then opening another file does.

[danesrood]

Mike

I've opened up another file within the current occurance of Excel and I still cannot pick up anything from the other worksheet.

[mikerickson]

If the little code in post #9 is not picking up cells in another workbook, I have no clue as to what might be going on.

[Biffbojangle]

Is there something special that needs to be altered for 2010?