Solved: Conditional Format VBA not using Range Name Value

[p45cal]

try replacing:
"=AND(B1=lValue,NOT(ISBLANK(B1)))"
with:
"=AND(B1=" & """" & lValue & """" & ",NOT(ISBLANK(B1)))"
The above should work.

However you'll still be left with the problem of not retaining the colours of conditional formatting. Use vba to colour the cells directly after you've pasted them without using conditional formatting. I suspect that the pasted cells will be selected then (?) so add a line such as
selection.interior.colorindex = 3
(not tried nor tested)