conditional formatting -dissapears

[Wanna Learn]

Hello I have a daily report that I update using a macro- (to get the
information from another report - that's ok ) In Column Q of this report I
have a conditional formatting-and it works, however, whenever I update the
report using the macro the conditional formatting does not work . How do i
correct this? thanks

 

[Dave Peterson]

Maybe you're pasting the new data into the old cells.

Instead of using
somerange.copy _
destination:=someotherrange

You could use pastespecial values???

I'd try it manually to see if that works. Then change the code.

 

[Wanna Learn]

Thanks Dave Yes I am pasting the new data into old cells. The macro I
run looks like this( I substituted the name of the real name with an X)
I'm not sure where your code goes. Again thanks
Sub ACD09()

ActiveCell.FormulaR1C1 = _

"=IF(ISERROR(VLOOKUP(RC3,'[X'!R1C1:R38C5,2,FALSE)),0,VLOOKUP(RC3,'[X'!R1C1:R38C5,2,FALSE))"
Range("D3").Select
Selection.Copy
Range("D429").Select
ActiveSheet.Paste
Range("L3").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _

"=IF(ISERROR(VLOOKUP(RC3,'[X'!R1C1:R37C5,3,FALSE)),0,VLOOKUP(RC3,'[X'!R1C1:R37C5,3,FALSE))"
Range("L3").Select
Selection.Copy
Range("L4:L29").Select
ActiveSheet.Paste
Range("L3").Select
Application.CutCopyMode = False
Selection.Copy
Range("N3").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _

"=IF(ISERROR(VLOOKUP(RC3,'[X'!R1C1:R37C5,4,FALSE)),0,VLOOKUP(RC3,'[X'!R1C1:R37C5,4,FALSE))"
Range("N3").Select
Selection.Copy
Range("N4:N29").Select
ActiveSheet.Paste
Range("N3").Select
Application.CutCopyMode = False
Selection.Copy
Range("Q3").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _

"=IF(ISERROR(VLOOKUP(RC3,'[X'!R1C1:R37C5,5,FALSE)),0,VLOOKUP(RC3,'[X'!R1C1:R37C5,5,FALSE))"
Range("Q3").Select
Selection.Copy
Range("Q4:Q29").Select
ActiveSheet.Paste
Range("Q3").Select
End Sub

 

[Dave Peterson]

You recorded a macro when you did this copy|paste stuff.

Record another one when you do Copy|paste special|formulas.

You'll have code that should work ok.

Thanks Dave Yes I am pasting the new data into old cells. The macro I
run looks like this( I substituted the name of the real name with an X)
I'm not sure where your code goes. Again thanks
Sub ACD09()

ActiveCell.FormulaR1C1 = _

"=IF(ISERROR(VLOOKUP(RC3,'[X'!R1C1:R38C5,2,FALSE)),0,VLOOKUP(RC3,'[X'!R1C1:R38C5,2,FALSE))"
Range("D3").Select
Selection.Copy
Range("D429").Select
ActiveSheet.Paste
Range("L3").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _

"=IF(ISERROR(VLOOKUP(RC3,'[X'!R1C1:R37C5,3,FALSE)),0,VLOOKUP(RC3,'[X'!R1C1:R37C5,3,FALSE))"
Range("L3").Select
Selection.Copy
Range("L4:L29").Select
ActiveSheet.Paste
Range("L3").Select
Application.CutCopyMode = False
Selection.Copy
Range("N3").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _

"=IF(ISERROR(VLOOKUP(RC3,'[X'!R1C1:R37C5,4,FALSE)),0,VLOOKUP(RC3,'[X'!R1C1:R37C5,4,FALSE))"
Range("N3").Select
Selection.Copy
Range("N4:N29").Select
ActiveSheet.Paste
Range("N3").Select
Application.CutCopyMode = False
Selection.Copy
Range("Q3").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _

"=IF(ISERROR(VLOOKUP(RC3,'[X'!R1C1:R37C5,5,FALSE)),0,VLOOKUP(RC3,'[X'!R1C1:R37C5,5,FALSE))"
Range("Q3").Select
Selection.Copy
Range("Q4:Q29").Select
ActiveSheet.Paste
Range("Q3").Select
End Sub

Maybe you're pasting the new data into the old cells.

Instead of using
somerange.copy _
destination:=someotherrange

You could use pastespecial values???

I'd try it manually to see if that works. Then change the code.