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
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.
Hi, what do you mean with "Paste Linked"?
Petr
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.
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
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
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
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
Last edited by ChinaReg; 01-23-2009 at 02:01 AM.
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
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
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
Hey Petr, Yes! It works beautiful!! Very appreciative, thanks and all the best to you! Problem solved!
Reg
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks