Changing the colour of a cell causes an error

[ewan1979]

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

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 

[Richard Buttrey]

Hi,
Precisely because the worksheet is protected. Unprotect it first and then reset it at the end of the macro.

Regards

[ewan1979]

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?

[barryleajo]

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

[ewan1979]

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.

[ewan1979]

Any ideas guys?

[Richard Buttrey]

Hi,

You are unprotecting the workbook not the worksheet. Unprotect the worksheet as I suggested.

Regards

[barryleajo]

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:

Please Login or Register  to view this content.

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

[Richard Buttrey]

Hi,

It also works fine on my PC using both XL 2003 & XL 2007.

Can you upload the actual workbook?

Regards

[ewan1979]

Hi All,

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

Thanks again.

[Richard Buttrey]

Hi All,

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

Thanks again.

Hi,

Does that mean your post #3 was wrong when you said you were already unprotecting the worksheet?

Regards