Changing cell color

[LePig]

Hi,

I am trying to change the color of a cell on a specific sheet when an action occurs either on button click on on open.

However it keeps throwing out a 1004 error code
Application-defined or object defined error.

The code exists in the main workbook




If ThisWorkbook.Sheets("Quotation Form").Range("A44").Interior.ColorIndex = 15 Then
MsgBox "i am white"
Sheet2.Range("A44").Interior.ColorIndex = 2
'Sheets("Quotation Form").Range("A42").Interior.ColorIndex = 2
'ThisWorkbook.Sheets("Quotation Form").Range("B44").Font.ColorIndex = 2
'Sheets("Quotation Form").Range("B42").Font.ColorIndex = 2
Else

End If


The confusing part is that it recognises the initial if statement because it will print the MsgBox but then finds fault with the part where i ask it to link to the specific spreadsheet. As you can see from the exert above i have tried a few different variations of linking to the sheet.

My spreadsheet contains lots of other code so to troubleshoot i tried to change the cell colour in a new spreadsheet project and the above code and it works fine.

Any ideas why it is not working would be greatly appreciated.

I find it confusing that the if statement can find the requested sheet but the statements inside cannot?

Thank you in advance

Many thanks

[Kenneth Hobs]

Not sure if you got it but if not, welcome to the forum!

I am guessing that you protected the structure? I like to put Protect code into Thisworkbook's Open event. Then code can change what it wants but the user can not depending on the protect options set. Change ken to suit.

Sheet2.Protect "ken", UserInterfaceOnly:=True

I would suggest some housekeeping for your code.
1. Use a sheet's code name or the sheet's name (on the tab) but don't use both. The former is more safe but most like to use the latter as it is more descriptive, typically.
2. You used Thisworkbook for the first line but then not in the 2nd. If you are running the code in the Open event, it will be the Activework so neither that object nor ThiwWorkbook object need be prefixed.
3. To structure your code. e.g. Use indentation for loops and logical If-Then-Else-EndIf. It makes code more readable and easier to troubleshoot. I like to set the Option in VBE to indent 2 spaces for a tab key press. The default is 5.
4. Use Debug menu's Compile before Running code. That did not help here but often will. I like to edit the toolbar and add it directly right next to Reset.
5. When use multiple ranges, you can do it like this:
[code]Range("A1,C1:E1,Q1").Interior.Color =2[/range]
6. When Protect is used in your project, don't forget to protect your VBAProject as the final task.

Feel free to ask for tips as you see patterns in your code that repeat. e.g. You can make an array to iterate sheet names or sheet indexes to say set the Protect password the same. Code is easier to maintain if just one line needs changed.

[LePig]

Hi Ken,

Thank you for the tips.

Will look into the above points.

Many thanks

[Paul_Hossler]

The "Quotation Form" sheet is protected, and I'm guessing the cell(s) are locked.

Unprotect the worksheet, change the cells, and the re-protect the worksheet and see if that works