Hi Glimmer,

Did you try the posted Event macro?

---
Regards,
Norman



wrote in message
oups.com...
ok my final solution---

probably can be improved and if you knwo it could be please feel free
to tell me :)

right its broken into three macros.
1 which detects the date change on the data sheet:

Private Sub Worksheet_Change(ByVal target As Range)

Application.ScreenUpdating = False

Dim rng3 As Range

Set rng3 = Range("B41")

If Not Intersect(target, rng3) Is Nothing Then
Run "FormatJuneStart"
Run "fp12m.FormatJuneEnd"
End If

Application.ScreenUpdating = True

End Sub

1 in the modules section cause i recorded it from excel - this is the
one which copies old format and paste specaisl the format over the new
range.

Public Sub FormatJuneStart()
Application.ScreenUpdating = False
Sheets("Fin Proj").Select
Range("F5:BM265").Select
Selection.Copy
Sheets("12 Mths Fin Proj").Select
Range("F5:BM265").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("BL247").Select
Range("F169:BM169").Select
Range("F6").Activate
Selection.Interior.ColorIndex = xlNone
Application.ScreenUpdating = True
End Sub


and finally the one which highlights the columns which are june

Public Sub FormatJuneEnd()
Application.ScreenUpdating = False
Dim Col
For Col = 6 To 65
If Month(Cells(5, Col)) = 6 Then
With Range(Cells(5, Col), Cells(265, Col)).Font
.Size = 10
.Bold = True
End With
End If
Next Col
Application.ScreenUpdating = True
End Sub


:) yay thank god thats done LOL thanks for your help guys