Run Time Error on change cell colour

[cadence]

Hello all the formula below Changes any cell in the range from blue to red. It does this only when a user enters a value in the cell, effectively overriding the cells formula based value.

My problem is this works fine on a mac. when I brought it over to a PC environment it it give me a 'Run-time error 1004'

(sorry I left all the range cell in, I was just worried that incase it was a simply syntax somewhere in that line itslef that the PC did not like

Private Sub Worksheet_Change(ByVal Target As Range)

'change Colour of cells from blue if formula based to red if data typed by user

If Not Intersect(Target, Range("BA5:BP66,BT7:CI55,BU60:BU64,BX60:BX64,CA60:CA64,CD60:CD64,BT55:CI66,BT59:CI59,CF7:CF55,CF65:CF66,DJ19:DJ21,DJ24,DL5:DM36,DJ41,DJ45,DJ48,DL41:DM48,DH50:DH51,DJ50:DJ51,DL50:DM53,DH63,DJ63,DL55:DM58,DL60:DM66,DU5:DV33,DU37:DV58,DZ8:EB8,ED6:EE27,ED5:EE27,ED31:EE66,EM5,EM5:EN12,EM16:EN29,EM33:EN38,DH63,AL5:AM26,AL30:AM49,AL53:AM66,AV5:AW16,AV20:AW29,AV33:AW53,AV55:AW63,CO5:CO66,CQ5:CR66,CY5:CY66,DA5:DB66,DJ5:DJ7,DJ14:DJ15,DJ17")) Is Nothing Then
If Target.HasFormula Then
Target.Font.ColorIndex = 11
Else
Target.Font.ColorIndex = 3
End If
End If
End Sub

PS I have also posted another problem regarding page breaks.

I would dearly love to make this a cross platform file. Any suggestions

much appreciated.

 

[wongm003]

I think it has something to do with your range... try taking away your range and replace with a single cell range and it works. I see that some of the range cells overlap and not sure if that will cause the error.

Here is code that will change the font to red anytime a user changes a formula within the spreadsheet and blue when they add a formula...

Private blnHadFormula As Boolean

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    'determine if cell has a formula when selecting the cell
    If Target.HasFormula Then
        blnHadFormula = True
    Else
        blnHadFormula = False
    End If

End Sub


Private Sub Worksheet_Change(ByVal Target As Range)

    'if the cell had a formula but no longer has one... change font to red
    If blnHadFormula And Not Target.HasFormula Then
        Target.Font.ColorIndex = 3
    'if cell did not have a formula but has one now... change font to blue
    ElseIf Not blnHadFormula And Target.HasFormula Then
        Target.Font.ColorIndex = 11
    End If

End Sub

 

[cadence]

ok will have another look, but its weird that it works on my mac, but finds an error in PC!