Linking Cells & Cell COLOR

[EverClear]

Hi there!!

I am using a vlookup formula to pull data from one worksheet into another. The data in the source worksheet is also formatted (NOT conditional formatting). How can I pull not only the data, but also the *cell formatting* from the source worksheet into the destination worksheet??

I found some VBA code here on the website, but it doesn't seem to work for my project:
Sub Worksheet_Calculate()
'apply cells colors from single-cell formula dependencies/links

Dim Cel As Range
Dim RefCel As Range
On Error Resume Next

For Each Cel In ActiveSheet.UsedRange

If Cel.HasFormula Then
Set RefCel = Evaluate(Mid(Cel.Formula, 2))
Cel.Interior.Color = RefCel.Interior.Color
End If

Next Cel
End Sub

Thanks for your help!!
Shawn

 

[Andrew Poulsom]

Where did you put that code? Can you give examples of the formulas you are using?

 

[EverClear]

sure! I placed the code in the destination worksheet's code module. here's an example of the vlookup formula i am using in the destination worksheet:

VLOOKUP($D$3,'FY14 KaBoom 5.30.13'!$B$80:$O$89,D15,FALSE)

So - the formula looks up a value in the source worksheet (KaBoom 5.30.13), and if it finds it, the value is returned. But the formatting isn't.