got error message "unable to set the ColorIndex..."

[associates]

Hi,

I was wondering if anyone here might be able to help me out here. It's kind of urgent.

I've been getting this error message without any error codes as follows
"unable to set the ColorIndex property of the interior class".

What happens is that i have a big file that i need to populate into spreadsheet with some of color formatting. It usually works with a small file.

So first it has to populate into worksheet "sheet3" and then into "data". Worksheet "sheet3" will be hidden from user. This way, user is only able to play around with worksheet "data". If they change anything in the "data", the color will change to notify that there has been a change there to the original one that is in worksheet "sheet3".

Here is my code

Private Sub CM_System_Click()
    With Worksheets("Data")
       .Unprotect
       .Range("A7:T3000").Clear
       .Range("H7:H3000").NumberFormat = "@"
    End With
    
    With Worksheets("Sheet3")
       .Unprotect
       .Range("A7:T3000").Clear
       .Range("H7:H3000").NumberFormat = "@"
    End With
    
    On Error GoTo ErrCheck
        filein = Application.GetOpenFilename()
        
        ImportCSV "Sheet3", filein
        ImportCSV "Data", filein
        
        ' end of the process
        MsgBox "Data Transfered... Completed"
    
Exit Sub

ErrCheck:
     'show the description of the error
     MsgBox Err.Description
End Sub

Sub ImportCSV(ByVal sheet As String, ByVal inputfile As String)
    slot = FreeFile
    Open inputfile For Input As slot
 
    Count = 0
    colnum = 0

    m = 0
 
    Countnewaccount = 0
 
    While Not EOF(slot)
        Count = Count + 1
        Line Input #slot, a$

        x = Split(a$, ",")
        
        If Count = 5 Then
            'skip first line
        ElseIf Count = 6 Then
            'skip second line
        ElseIf Count >= 7 Then
            For i = 0 To (UBound(x))
                c$ = Chr$(65 + i) + Trim(Str$(Count))
            
                With Worksheets("Data")
                    With .Range(c$)
                    .FormatConditions.Delete
                    .FormatConditions.Add Type:=xlCellValue, Operator:=xlNotEqual, Formula1:="=my" & c$
                    .FormatConditions(1).Interior.ColorIndex = 37
                    End With
                End With
            
                rowline1 = "my" & c$
                mysht = "=Sheet3!R" & CStr(Count) & "C" & CStr(i + 1)
                With ActiveWorkbook
                   .Names.Add Name:=rowline1, RefersToR1C1:=mysht
                End With
            
                Set myRange = Worksheets(sheet).Range(c$)
                'enter data into excel
                If i = 7 Then
                    mytemp = Replace(x(i), "'", "")
                    myRange.Value = mytemp
                Else
                    myRange.Value = x(i)
                End If

            Next i

            i = 8   ' fill the total in the column H
            mycell = Chr$(65 + i) + Trim(Str$(Count))   ' Column S
            mytemp = "=" & mycell & "-my" & mycell
            mycell1 = Chr$(65 + i + 1) + Trim(Str$(Count))  'Column T for difference in sum

            Worksheets("Data").Range(mycell1).Formula = mytemp
            mytemp1 = "=sum(K" & Trim(Str$(Count)) & ":V" & Trim(Str$(Count)) & ")"
            Worksheets("Data").Range(mycell).Formula = mytemp1  ' for summing one whole row
            
            With Worksheets("Data")
                With .Range(mycell)
                'With myRange
                .FormatConditions.Delete
                .FormatConditions.Add Type:=xlCellValue, Operator:=xlNotEqual, Formula1:="=my" & mycell
                .FormatConditions(1).Interior.ColorIndex = 3
                End With
            End With
            
        End If
    Wend
End Sub

wonder if anyone might be able to point out what i should do to avoid getting the above error message.

Thank you in advance

[rylo]

Hi

If this works on a small sheet, then my first thought would be that you have too many defined names.

As the worksheets are the same, why don't you build a conditional format formula that references the same cell in sheet3. You have the current cell address when you paste the data, so make the reference to that cell in sheet3.

hth

rylo