Thread: Changing the colour of a cell causes an error

Hi,

I am using a form to populate a row on a spreadsheet. The code below works when the worksheet is not protected, but when it is the line below causes an error. Does anyone know why changing the colour of the cell is causing a issue?

Worksheets("Master").Cells(Row, 9).Interior.ColorIndex = 15 'Change colour of cell

PHP Code: 

If valid Then

'Unprotect spreadsheet
ActiveWorkbook.Unprotect ("fx")

    'Find the last row
    Row = Range("A65536").End(xlUp).Row
    Row = Row + 1

    'Populate next row
    Worksheets("Master").Cells(Row, 1).Value = customerInput.Value
    Worksheets("Master").Cells(Row, 2).Value = currencyPair.Value
    Worksheets("Master").Cells(Row, 3).Value = direction.Value
    Worksheets("Master").Cells(Row, 4).Value = amount.Value
    Worksheets("Master").Cells(Row, 5).Value = fixType.Value
    Worksheets("Master").Cells(Row, 6).Value = todayDate.Value
    Worksheets("Master").Cells(Row, 7).Value = fixTime.Value
    Worksheets("Master").Cells(Row, 8).Value = dealer.Value
    
    'Change colour of cell and put placed in confirmed order
    Worksheets("Master").Cells(Row, 9) = "Placed" 'Put Placed in cell
    Worksheets("Master").Cells(Row, 9).Interior.ColorIndex = 15 'Change colour of cell

    'Close form
    Unload Me

    'Save spreadsheet
    ActiveWorkbook.Save
    
    Protect spreadsheet
    ActiveWorkbook.Protect ("fx")
    

End If 


Hi Richard,

I am am unprotecting the worksheet at the start and protecting it at the end and this work when I don't include the line

Worksheets("Master").Cells(Row, 9).Interior.ColorIndex = 15 'Change colour of cell

However when I do include it I get an error. Any ideas?

ewan1979
What is the error reporting?
Presumably "Placed" gets placed in the cell correctly?

Hi barryleajo,

Yes "placed" gets placed it is changing the colour of thew cell that causes the issue.

The error message is "Run time error 1004 Application defined or object defined error".

Any help would be greatly appreciated.

Any ideas guys?

ewan1979
The code snippet works fine for me on my PC.
I suspect its not a sheet protection issue because the cell is written-to successfully by the previous statement.
I have just noticed that you are using Excel 2003 which may deal with colours differently.
Try a different way of setting the colour, for example:

Worksheets("Sheet1").Cells(row, 9).Interior.Color = RGB(100, 200, 300)

Note you will need to get the correct RGB combination for your grey colour.
May help
Barry

Hi All,

Than you kindly for all your help the unprotect worksheet idea worked.

Thanks again.