Conditional formatting nightmare

tomleitch

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

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

jmacleary

Well-known Member
Joined
Oct 5, 2015
Messages
1,056
Office Version
  1. 365
  2. 2007
Platform
  1. 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
188
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
1,056
Office Version
  1. 365
  2. 2007
Platform
  1. 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
188
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
1,056
Office Version
  1. 365
  2. 2007
Platform
  1. 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,129,479
Messages
5,636,575
Members
416,925
Latest member
malamutus

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top