+ Reply to Thread
Results 1 to 11 of 11

Paste Link Colors to another worksheet

  1. #1
    Registered User
    Join Date
    01-21-2009
    Location
    Nanning, China
    MS-Off Ver
    Excel 2003
    Posts
    12

    Paste Link Colors to another worksheet

    Is there a macro that will copy the color of a cell in "worksheet 1" to a cell "worksheet 2" when the 2 worksheets are Paste Linked? Thanks
    Last edited by ChinaReg; 01-23-2009 at 07:09 AM.

  2. #2
    Registered User
    Join Date
    01-22-2009
    Location
    Brno, Czech Republic
    MS-Off Ver
    Excel 2003
    Posts
    29
    Hi, what do you mean with "Paste Linked"?
    Petr

  3. #3
    Registered User
    Join Date
    01-21-2009
    Location
    Nanning, China
    MS-Off Ver
    Excel 2003
    Posts
    12
    Hello PetrH thanks for your reply. Paste Linked means the two worksheets are connected via COPY>EDIT>PASTE SPECIAL>PASTE LINK. Which means that data entered in Worksheet 1 will automatically be transferred to the specified location in Worksheet 2. The data or formulas will transfer easily enough but cell colors will not.

  4. #4
    Registered User
    Join Date
    01-22-2009
    Location
    Brno, Czech Republic
    MS-Off Ver
    Excel 2003
    Posts
    29
    Hi, as far as I know you would have to write the procedure in VBA. This procedure would browse the specific range and check if the color is the same as the original refered cell. The procedure would be started by some button (for time to time refresh) or by SheetChange event (this would start the procedure after each change and therefor could slow down your work). Try to consider what is better for you.
    Regards
    Petr

  5. #5
    Registered User
    Join Date
    01-21-2009
    Location
    Nanning, China
    MS-Off Ver
    Excel 2003
    Posts
    12
    Hi Petr, my knowledge of VBA is virtually nil. I have been hunting the internet to try and find some code but so far have been unsuccessful. I'm hoping someone will pick up this thread. So far, no luck. Reg

  6. #6
    Registered User
    Join Date
    01-22-2009
    Location
    Brno, Czech Republic
    MS-Off Ver
    Excel 2003
    Posts
    29
    Hi Reg, hopefuly I can help you. Please chose one of the option how to launch the macro and we can start to write a code. Regards
    Petr

  7. #7
    Registered User
    Join Date
    01-21-2009
    Location
    Nanning, China
    MS-Off Ver
    Excel 2003
    Posts
    12
    Thanks Petr, I will attached my spreadsheet so that you can better understand what I am trying to do. There are Two worksheets, one is titled Working Copy, the second Worksheet is titled Finished Schedule. In the Working Copy when I input a number into the columns labeled TR, the text which appears in the Day/Date columns are colored. I have linked the Day/Date columns in Working Copy to the Finished Schedule. The data transfers to Finished Schedule okay but the colors do not. I hope to find a macro that will also give the linked data the same colors in both sheets. The worksheet is not that big so if it updates after each entry, it probably won't cause too much delay so I will forgo the button if possible. Many thanks for you help.. Reg
    Attached Files Attached Files
    Last edited by ChinaReg; 01-23-2009 at 02:01 AM.

  8. #8
    Registered User
    Join Date
    01-22-2009
    Location
    Brno, Czech Republic
    MS-Off Ver
    Excel 2003
    Posts
    29
    Hi. I see you have already made a macro changing the color of the text. So add simply one more line to each Case command to change the corresponding cell on next sheet too. Regards
    Petr

  9. #9
    Registered User
    Join Date
    01-21-2009
    Location
    Nanning, China
    MS-Off Ver
    Excel 2003
    Posts
    12
    Hi Petr, yes I also got that code from someone on this forum. I have no idea how to add the code you suggested. If you can lead me by the hand, I would be very appreciative. Thanks again for your trouble.. Reg

  10. #10
    Registered User
    Join Date
    01-22-2009
    Location
    Brno, Czech Republic
    MS-Off Ver
    Excel 2003
    Posts
    29
    Replace the code by following.
    Does it work?
    Regards
    Petr

    Private Sub Worksheet_Change(ByVal Target As Range)

    If Intersect(Target, Union(Range("E3:E20"), Range("I3:I20"), Range("M3:M20"), Range("Q3:Q20"), Range("U3:U20"), Range("Y3:Y20"))) Is Nothing Then Exit Sub
    ChangedRow = Target.Row
    ChangedCol = Target.Column

    Select Case Target.Value
    Case 1: TextColor = 4
    Case 2: TextColor = 13
    Case 3: TextColor = 9
    Case 4: TextColor = 5
    Case 5: TextColor = 7
    Case 6: TextColor = 41
    Case 7: TextColor = 46
    Case 8: TextColor = 12
    Case Else: TextColor = 0
    End Select

    Target.Offset(, 2).Font.ColorIndex = TextColor
    CorrespondingCol = (ChangedCol - 1) / 4 + 3
    Worksheets("Finished Schedule").Cells(ChangedRow + 1, CorrespondingCol).Font.ColorIndex = TextColor
    End Sub

  11. #11
    Registered User
    Join Date
    01-21-2009
    Location
    Nanning, China
    MS-Off Ver
    Excel 2003
    Posts
    12
    Hey Petr, Yes! It works beautiful!! Very appreciative, thanks and all the best to you! Problem solved!

    Reg

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1