What are possible causes of VBA clearing Conditional Formatting

[philwojo]

Hello, I have several version of a workbook I am trying to implement, as I've added and changed code I've saved in different revisions.

Right now I am trying to track down why Conditional Formatting is being cleared from one range of cells on a newer revision of the WB compared to an older revision.

I am trying to compare changes I've made, but can anyone tell me what some common causes would be to look for that could cause CF to be cleared from cells?

I do use Clear.Contents in several parts of my code, and I checked to make sure it is not just a Clear, and I can't find code that would indicate that anywhere.

I'm looking at all of the other code I have put in and had help with and I'm just not seeing anything that should be causing this, but I know I could easily be overlooking something.

Any help would be greatly appreciated.

I know I could post all of my code, but I'm holding off on doing that for now, unless someone requests me to do that.

Phil

 

[Joe4]

If you want to debug this yourself, try doing some testing where you exit out of your code at different points (with an "Exit Sub" command).
Check to see if the Conditional Formatting still exists at that point. If it does, remove that "Exit Sub", and add one a little further on and repeat the process.
This should help you zero in on exactly where the Conditional Formatting is being removed.

 

[RoryA]

Do you copy and paste any ranges?

 

[philwojo]

OK, I stepped through it one line at a time, and I am not sure why, but when this bit of code runs it causes the CF to go away:

Worksheets("Raw Data").Activate
    Range("B2:B" & Cells(Rows.Count, "B").End(xlUp).Row).Copy Sheets("PIF File Checker").Range("B7")

Now, the sheet that is active at the time is "Raw Data", but the problem sheet is "PIF File Checker", I am losing the CF in B6:B6000 when that code runs.

It is also taking what looks to be the data on the "PIF File Checker" sheet from B2:B5 and putting it in to B7.

I am not sure why, based on the code above, it would select the data on this other sheet and not on the "Raw Data" sheet?

I am still learning VBA stuff, so it is probably something simple I am overlooking, maybe an interaction from another call in other code that is maybe leaving the "PIf File Checker" sheet active, but I'd love any guidance anyone may have.

Thanks for the direction so far Joe.

Phil

 

[Joe4]

It is just as Rory guessed. You are copying over a range from another sheet onto the range on your other sheet that has the Conditional Formatting.
When you Copy over a range like that, it copies over all the formatting too. So your Conditional Formatting is being overwritten with the Formatting on the range that you are copying from.

 

[Rick Rothstein]

OK, I stepped through it one line at a time, and I am not sure why, but when this bit of code runs it causes the CF to go away:

Worksheets("Raw Data").Activate
    Range("B2:B" & Cells(Rows.Count, "B").End(xlUp).Row).Copy Sheets("PIF File Checker").Range("B7")

Now, the sheet that is active at the time is "Raw Data", but the problem sheet is "PIF File Checker", I am losing the CF in B6:B6000 when that code runs.

Your code line is copying everything about the source cells (values and formats including Conditional Formats included) over top of the destination cells. Maybe you want to copy the cell and separately PasteSpecial values only?

 

[philwojo]

OK, is there a way to copy it over without destroying the CF I have in place?

Again, still learning.

 

[philwojo]

I do want to do that, I just want to paste the raw data as values, so with that code I have there now how could I update or change that?

 

[RoryA]

Replace those two lines with

With Worksheets("Raw Data")
    .Activate
    .Range("B2:B" & .Cells(Rows.Count, "B").End(xlUp).Row).Copy
End With
Sheets("PIF File Checker").Range("B7").PasteSpecial xlPasteValues

You could probably skip activating the sheet but I'm not sure what other subsequent code you have that is assuming it is active.

 

[Rick Rothstein]

I do want to do that, I just want to paste the raw data as values, so with that code I have there now how could I update or change that?

I hate activating sheets simply to work with them, but since that is your style, try this in place of what you posted...

[table="width: 500"]
[tr]
	[td]Worksheets("Raw Data").Activate
    Range("B2:B" & Cells(Rows.Count, "B").End(xlUp).Row).Copy
    Sheets("PIF File Checker").Range("B7").PasteSpecial xlPasteValues[/td]
[/tr]
[/table]