Run Time Error on change cell colour

cadence

Well-known Member
Joined
Dec 6, 2005
Messages
528
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

Code:
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.
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

wongm003

Board Regular
Joined
Aug 8, 2005
Messages
237
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...

Code:
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

Well-known Member
Joined
Dec 6, 2005
Messages
528
ok will have another look, but its weird that it works on my mac, but finds an error in PC!
 

Forum statistics

Threads
1,136,269
Messages
5,674,741
Members
419,525
Latest member
helensesc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top