Copying formatting to linked file?

[Hambone70]

I have two linked files: a weekly schedule and a daily schedule. I have a simple macro that colors a cell's font in the weekly schedule (CTRL-B black, CTRL-R red).

When I use the macro to color the font in a cell in the weekly schedule, I would like the font in the corresponding linked cell in the daily schedule to change color, too.

Is this possible? Thanks!

[Hambone70]

Anyone? Perhaps this would be better in the general forum.

[TMS]

I would suggest you post a sample workbook.

Regards, TMS

[Hambone70]

Here's an extract.

CTRL-B and CTRL-R macros change the text color of a cell in the weekly schedule.

I would like the linked cell in the daily schedule to reflect the change of cell color in the weekly schedule. So, for example, when I CTRL-R in a cell in the weekly schedule to change the text color to red (like Harry in the weekly example), the cell containing Harry in the daily schedule changes to red, too.

[TMS]

In theory, you can do something like this:

Please Login or Register  to view this content.

I'm afraid I'll have to leave it with you to work out how to generalise it across workbooks, worksheets, dates, whatever.

Regards, TMS

[Hambone70]

I'm not quite sure how to incorporate that, but I'll try.

Thanks!

[TMS]

You're welcome.

Well, if you only have the two files, it just replaces the existing macro. However, you would need to work out (somehow) the name of the workbook that needs to be changed, or the sheet within the workbook, or whatever.

It's just demonstrating that you can store the address of the cell being changed by the macro and then use that later to modify the same cell in a different workbook/worksheet.

By the way, Ctrl-b is not a great choice for a short cut as, by default, most Windows applications, including Excel, will use that for setting Bold font.


Regards, TMS



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

[Hambone70]

I'll only be using this between the two workbooks.

The macro does work, but it only changes the same cell reference in the daily workbook.

Is there any way to have it change any cell that is referenced from the weekly schedule workbook, irrespective of where that cell is located? The linked cells aren't necessarily in the same position in the two workbooks, as they are in the example.

Thanks again!

[TMS]

In the example, you use INDEX/MATCH ROW/COLUMN to return a value from the other workbook. In that instance, they will have the same values in the same cells.

So, if you change cell A1 in Workbook 1, Worksheet 1, it will change cell A1 in Workbook 2, Worksheet 1.

If you want to change a different cell depending on the contents of the cell, you will need to store the contents of the cell being highlighted and use that to MATCH the value in the same column on the same worksheet in the other workbook. That shouldn't be a problem unless the names are duplicated.

Regards, TMS