Thanks for replying...

...the workbook consists of 7 worksheets. One worksheet contains all of
the data with the other 6 performing a 3d reference to it i.e.

worksheets are named;

Total
CUN
CSI
CNI
IRM
DUP
Data

One cell in the Total worksheet could have a formula stating =Data!H32,
so infact there are no values in each cell.

As a work round I did copy the 'Total' worksheet and paste special
values only into another sheet using the VB code but it didn't work.
This is how mine looks;



Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
' adjust range below to meet your needs
If Not Intersect(Target, Me.Range("A1:DM114")) Is Nothing Then
With Target
Select Case UCase(.Value)
'Case Is 2500: .Interior.ColorIndex = ? do you have
values 2500?
Case Is 1000: .Interior.ColorIndex = 3
Case Is 500: .Interior.ColorIndex = 13
Case Is 250: .Interior.ColorIndex = 46
Case Is 100: .Interior.ColorIndex = 6
Case Is 50: .Interior.ColorIndex = 10
Case Is 0: .Interior.ColorIndex = 5
'etc.
End Select
End With
End Ifws_exit:
Application.EnableEvents = True
End Sub

I've tried to match the colours as best I can!?

Thanks and regards

Dave


--
GoldDave
------------------------------------------------------------------------
GoldDave's Profile: http://www.excelforum.com/member.php...o&userid=27750
View this thread: http://www.excelforum.com/showthread...hreadid=472580