Change Cells Colour: Range Error (Solved almost)

[cadence]

Hello all,

The formula below works on a mac but whe I try it on a PC I get an

Run-Time error '1004':
Method 'Range' of object'_worksheet' failed

The Function basically takes any cell in range with a formula and has it blue. If the formula is overwritten by the user it turns the cells colour Red.

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("AS63,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,ED5:EE27,ED31:EE66,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

With a one cell range it works, but really need a way to have all these cells included.

Any thoughts?

Cheers

 

[jindon]

Hi

You can only enter 255 characters in the bracket to specify the Range.

You could use Union method or Select Case statement...

 

[cadence]

Jinbon that good to know

You could use Union method or Select Case statement...

How would I use a Union method? I have used Select Case before, but am unsure how to incorporate it into this problem?

Else could I have the same macro and just split the range up?

Thanks

 

[Scott Huish]

With Union:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim x As Range
'change Colour of cells from blue if formula based to red if data typed by user
Set x = Union(Range("AS63,BA5:BP66,BT7:CI55,BU60:BU64,BX60:BX64,CA60:CA64,CD60:CD64,BT55:CI66,BT59:CI59,CF7:CF55"), _
            Range("CF65:CF66,DJ19:DJ21,DJ24,DL5:DM36,DJ41,DJ45,DJ48,DL41:DM48,DH50:DH51,DJ50:DJ51"), _
            Range("DL50:DM53,DH63,DJ63,DL55:DM58,DL60:DM66,DU5:DV33,DU37:DV58,DZ8:EB8,ED5:EE27,ED31:EE66"), _
            Range("EM5:EN12,EM16:EN29,EM33:EN38,DH63,AL5:AM26,AL30:AM49,AL53:AM66,AV5:AW16,AV20:AW29,AV33:AW53"), _
            Range("AV55:AW63,CO5:CO66,CQ5:CR66,CY5:CY66,DA5:DB66,DJ5:DJ7,DJ14:DJ15,DJ17"))
If Intersect(Target, x) Is Nothing Then Exit Sub
If Target.HasFormula Then
    Target.Font.ColorIndex = 11
Else
    Target.Font.ColorIndex = 3
End If
End Sub

 

[cadence]

Thanks HotPepper

add one more to the memory bank.