Noob VBA / Macro help

[MadCallidus]

I'm sure this is gonna be simple for anyone who knows what they're doing...

I'm running some code in my workbook - this simply changes cell colour based on value:

Public Sub Worksheet_Change(ByVal Target As Range)

Dim icolor As Integer



If Not Intersect(Target, Range("c8f25")) Is Nothing Then

Select Case Target

Case 1, 10, 19, 28, 37, 46, 55, 64, 73, 82, 91, 100, 109, 118, 127, 136, 145, 154, 163, 172, 181, 190, 199

icolor = 39

Case 2, 11, 20, 29, 38, 47, 56, 65, 74, 83, 92, 101, 110, 119, 128, 137, 146, 155, 164, 173, 182, 191, 200

icolor = 37

Case 3, 12, 21, 30, 39, 48, 57, 66, 75, 84, 93, 102, 111, 120, 129, 138, 147, 156, 165, 174, 183, 192, 201

icolor = 34

Case 4, 13, 22, 31, 40, 49, 58, 67, 76, 85, 94, 103, 112, 121, 130, 139, 148, 157, 166, 175, 184, 193, 202

icolor = 35

Case 5, 14, 23, 32, 41, 50, 59, 68, 77, 86, 95, 104, 113, 122, 131, 140, 149, 158, 167, 176, 185, 194, 203

icolor = 36

Case 6, 15, 24, 33, 42, 51, 60, 69, 78, 87, 96, 105, 114, 123, 132, 141, 150, 159, 168, 177, 186, 195, 204

icolor = 40

Case 7, 16, 25, 34, 43, 52, 61, 70, 79, 88, 97, 106, 115, 124, 133, 142, 151, 160, 169, 178, 187, 196, 205


icolor = 38

Case 8, 17, 26, 35, 44, 53, 62, 71, 80, 89, 98, 107, 116, 125, 134, 143, 152, 161, 170, 179, 188, 197, 206

icolor = 24

Case 9, 18, 27, 36, 45, 54, 63, 72, 81, 90, 99, 108, 117, 126, 135, 144, 153, 162, 171, 180, 189, 198, 207


icolor = 15


Case Else

'whatever


End Select



Target.Interior.ColorIndex = icolor

End If

End Sub

This works for any manually entered text, but wont change colour for any referenced cells (using the code ='\\nas\drive\folder\[other_workbook.xlsm]sheet'!cell)

I have a small macro that refreshes all referenced data which works fine.

Is there a way I can get my colour-change VBA to run for any data refreshes. Or even how I can make a Macro that runs it at the press of a button?

Many thanks.

Andy