Conditional formatting nightmare

tomleitch

Board Regular
Joined
Jan 3, 2012
Messages
185
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:

Code:
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
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

jmacleary

Well-known Member
Joined
Oct 5, 2015
Messages
940
Office Version
2007
Platform
Windows
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

Board Regular
Joined
Jan 3, 2012
Messages
185
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

Well-known Member
Joined
Oct 5, 2015
Messages
940
Office Version
2007
Platform
Windows
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

Board Regular
Joined
Jan 3, 2012
Messages
185
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

Well-known Member
Joined
Oct 5, 2015
Messages
940
Office Version
2007
Platform
Windows
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:
Code:
Range(Cells(9, "A"), Cells(Lrow, lcol - 1)).Copy

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

Watch MrExcel Video

Forum statistics

Threads
1,095,178
Messages
5,442,847
Members
405,202
Latest member
Mira_Xcel

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...
Top