Automatically updating the format of a cell based on the format of another cell

[deanfletcher1901]

I have a holiday planner which has a separate sheet for each month, the sheets are numbered from 1-12. Each sheet has a key at the top which contains a list of values to put in for reasons for time off, e.g. "H" for holiday, "S" for sickness. The key is pulled from another sheet called "13" in the workbook so I don't have to update the values in each of the sheets.


I want the formatting of the cells in sheets 1-12 to update when I change the formatting in sheet 13. SO if A1 in sheet 13 changes to red, A1 in sheets 1-12 will also change to red.


I know conditional formatting cannot do this, it needs to be done using a macro but I only have very basic knowledge of macros.


Any help would be greatly appreciated

 

[Luke M]

This should help get you started. I tried to comment it out with guidance. I wasn't sure if you only needed to copy formatting from top rows or not. But you can adjust the ranges as needed.

[pre]

Sub CopyData()
Dim i As Long
Dim myRange As Range

'Where is the range of cells with formatting that you care about?
Set myRange = Worksheets("13").Range("A1:J1")
Application.ScreenUpdating = True

'Loop through a range of numbers
'representing our 12 sheets
For i = 1 To 12
myRange.Copy

'The "" are to change the number i into a string representing
'the worksheet name
'
'We then use the Paste Special - formats method. Note the range
'You are pasting to.
Worksheets("" & i).Range("A1:J1").PasteSpecial (xlPasteFormats)
Next
'turn of the copy method
Application.CutCopyMode = False

Application.ScreenUpdating = True
End Sub

[/pre]