Clearcontents, not conditional format.

[user21136]

Hi,

Using Excel 2003.

I'd like to clear some cells using VBA...

            .Range("C5:C35").ClearContents
            .Range("D5:D35").ClearContents
            .Range("E5:E35").ClearContents
            .Range("H5:H35").ClearContents

These cells all have conditional formating from row 6 to 36.

In row 6, conditional format for example: Formula is =C5<>""

When I run the macro it clears the contents of C5:C35 & H5:H35 fine BUT where there are values in D5:D35 & E5:E35 it clears the conditional formating too.

Can't figure out why, any ideas please?

Regards, Glenn.

 

[pedie]

try

[FONT=Verdana]Option Explicit
Sub try()
Dim c As Range
For Each c In Range("C5:C35,D5:D35,E5:E35,H5:H35")
 If c.Value <> "" Then c.ClearContents
Next
End Sub
[/FONT]
[FONT=Courier New][/FONT]

 

[user21136]

Hi pedie,

Thank you but it's still doing exactly the same as above.

Weird!

I guess worst case I could copy/replace the Conditional Formatting from other cells outside the range.

Cheers, Glenn

 

[user21136]

Apologies,

Tt seem this problem is cause by Worksheet_Change code. I've deleted it and Clearcontents is now does NOT delete the Conditional Format.

A bit off topic but it there a way to disable Worksheet_Change code within another macro?

Thanks.
Glenn.

Nevermind... I had: Application.EnableEvents = False - in the wrong spot. Wow!