ok - so have played around a bit and now have the following - which
works when you type new values into the sheet.

Code:
--------------------
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim WatchRange As Range
Dim CellVal As String
'If Target.Cells.Count 1 Then Exit Sub
'If Target = "" Or Not IsNumeric(Target) Then Exit Sub
CellVal = Target
Set WatchRange = Range("G1:DX42")

If Not Intersect(Target, WatchRange) Is Nothing Then
Select Case CellVal
Case "VRF"
Target.Interior.ColorIndex = 5
Case "Alignment Review"
Target.Interior.ColorIndex = 10
Case "BSD Big Picture Event"
Target.Interior.ColorIndex = 6
Case "Cost Benefit Workshop"
Target.Interior.ColorIndex = 46
Case "DSB Detailed Event"
Target.Interior.ColorIndex = 45
Case "IT Executive Review"
Target.Interior.ColorIndex = 45
Case "IT Supplier Proposal Issued"
Target.Interior.ColorIndex = 45
Case "Plan Complete"
Target.Interior.ColorIndex = 45
Case "Requirements Solution Workshop"
Target.Interior.ColorIndex = 45
Case "Viability Report"
Target.Interior.ColorIndex = 45
Case "Landing Slot"
Target.Interior.ColorIndex = 45
End Select
End If


End Sub
--------------------

However I would like to be able to paste in updated data and for the
sheet to automatically format.

I can do this with individual cells, but if I try to copy and paste
more than one cell I get Datatype mismatch.

Anyone know what I need to do to allow me to copy and paste a whole
spreadsheet of new information in, and for the sheet to accept it and
automatically update with formatting???


--
Nuttychick
------------------------------------------------------------------------
Nuttychick's Profile: http://www.excelforum.com/member.php...o&userid=23017
View this thread: http://www.excelforum.com/showthread...hreadid=539786