Conditional formatting nightmare

[tomleitch]

Hi,

Wondering if anyone can help or advise


I have a sheet that has 3 columns with multiple conditional formatting rules to basically change the cell fill to red, orange or green depending on the date and what is in adjacent cells.

The conditional formatting is working fine on my sheet but when I try and copy and pastespecial in vba it is always filling them red - and not seeming to see the other rules that are overriding the red rule (these are formula rules).

The upshot is that I am trying to paste into an email (I've got the email part fine) but it's not picking up some of the formatting rules and all the cells end up getting pasted into the email with red fill - obviously not what I want to do.


I've tried various additions to the code that I've found online to replace the conditional formatting with fixed formatting but still the same result - it's not picking up the other overriding formula conditional formats and I end up with all red boxes.


Can anyone help?


The copy paste code I'm using is this:

Range(Cells(9, "A"), Cells(Lrow, lcol - 1)).Copy

With Sheets("EMAIL TOOL").Range("A1")
     .PasteSpecial xlPasteFormats
     .PasteSpecial xlPasteValues

Any help would be greatly appreciated
Thanks
Tom

 

[jmacleary]

Hi there. The PasteSpecial xlPasteFormats should copy over the conditional formatting. Have you checked A1 to see if any conditional formatting rules been copied in? It may be that its copying ok but the formula is/are wrong - its possible you have absolute addresses in the formula (e.g. $X$3) so all conditions would be the same.

 

[tomleitch]

Hi and thanks for getting back to me.... so what I think is happening is this:

I'm copying from sheet 1 to sheet 2 and from sheet 2 into an email

Sheet 1 contains the conditional formatting similar to this example:

A1 fill is green if B1, C1, D1 and E1 contain data.

I only want to copy the A column

So when it pastes into sheet 2 then its looking at the B1 C1 etc of Sheet 2 and seeing that they are empty and it is turning red (the format for if one of them is not containing data).

Does that make sense?

Not sure of a way around it yet - but I think that is the problem


Cheers
Tom

 

[jmacleary]

Yes, I think you're right. how about you set up your conditional formatting rules correctly in sheet2 and then just paste values from sheet 1 to sheet 2? That should leave the conditional formatting correct.

 

[tomleitch]

Trouble is that I need them to go from sheet 2 into the email.

Whenever I do that it seems to get the fill colour wrong..... I can't seem to find a way around it other than to possibly copy the range as an image and paste that into the email - but I don't parfticularly want to go that route as I want to have selectable text in it.


Cheers
Tom​

 

[jmacleary]

Hi Tom

I'm now confused. If you set the conditional formatting correctly in Sheet2 and just type something in A1 does the condition work correctly? My suggestion in post 4 above should result in you removing the formatting element in your code, so it would read:

Range(Cells(9, "A"), Cells(Lrow, lcol - 1)).Copy

With Sheets("EMAIL TOOL").Range("A1")
     .PasteSpecial xlPasteValues