Prevent Custom Theme from Affecting Spreadsheets?

[bencar]

Hi. i got a win 10 custom theme installed. But it affects the background color of my spreadsheets. Is there a vba that can black any themes from affecting them?

 

[bencar]

Re: How Do I Prevent Custom Theme from Affecting Spreadsheets?

If the sheet mentioned on the message box does not change colors, then VBA is overridden by the theme.
Please perform another test; apply some conditional formatting, such as cells turning green if the value is above zero, and see if the color displays correctly.

Hi worf,

Yes the conditional formatting works. i was able to get numbers above a certain value colored. What do you suggest?

 

[Worf]

Re: How Do I Prevent Custom Theme from Affecting Spreadsheets?

If the CF color displays correctly, use this procedure:

• Select all worksheet cells
• Go to conditional formatting>new rule>use formula> “=TRUE”
• Choose a format with white background and grey cell borders
• If you need to perform this action multiple times, VBA can be employed.

 

[bencar]

Re: How Do I Prevent Custom Theme from Affecting Spreadsheets?

If the CF color displays correctly, use this procedure:

• Select all worksheet cells
• Go to conditional formatting>new rule>use formula> “=TRUE”
• Choose a format with white background and grey cell borders
• If you need to perform this action multiple times, VBA can be employed.

Hi worf,

I'm confused. Will I have to perform this every single time I open excel??

 

[Worf]

Re: How Do I Prevent Custom Theme from Affecting Spreadsheets?

Yes, unless you have some VBA to do it for you. I will prepare the code.

 

[bencar]

Re: How Do I Prevent Custom Theme from Affecting Spreadsheets?

Yes, unless you have some VBA to do it for you. I will prepare the code.

This makes no sense. Why would I go through the headache of going through these steps every time I open excel???

 

[Worf]

Re: How Do I Prevent Custom Theme from Affecting Spreadsheets?

There will be some code in the personal macro workbook. Running it, which is a one-click procedure, will format all worksheets on all open workbooks.

Is this a suitable solution for you?

 

[bencar]

Re: How Do I Prevent Custom Theme from Affecting Spreadsheets?

There will be some code in the personal macro workbook. Running it, which is a one-click procedure, will format all worksheets on all open workbooks.

Is this a suitable solution for you?

Hi worf,

As long as all I need to do is open excel normally without going through ANY step.

 

[Worf]

Re: How Do I Prevent Custom Theme from Affecting Spreadsheets?

This can be done, but will require some setup. After that, it should work automatically.
First thing is to have a workbook opening when Excel starts; follow the instructions below. The code in this XLSM file should go where indicated. If all goes well, you will get messages for the following events:

• Excel started
• A workbook was opened
• A new workbook was created

Tell me when you have this working and we can move on.

' Module1 or equivalent


Sub Auto_Open()

MsgBox ThisWorkbook.Name & " was just opened.", 64, "Excel launched"

End Sub

********************************************

' ThisWorkbook module


Private WithEvents App As Application

Private Sub App_NewWorkbook(ByVal Wb As Workbook)
MsgBox "New Workbook: " & Wb.Name
End Sub

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
MsgBox "Opened: " & Wb.Name
End Sub

Private Sub Workbook_Open()
Set App = Application
End Sub

If you’re always using Excel to work on the same workbook or create new workbooks based on the same template. You can set Excel to open that workbook or template when it starts.
To open the same workbook every time you start Excel, in the File menu click > Save as > Computer, Browse and then browse to the XLSTART folder.

• In a clean install of Windows Vista and later, the path to the XLSTART folder is usually:
  
  Office 2013 – C:\Users\(User name)\AppData\Microsoft\Excel\XLSTART

Office 2013 C2R – C:\Users\(User name)\AppData\Microsoft\Excel\XLSTART

• If you upgraded from another version of Windows, the path could also be:
  
  Office 2013 – C:\Program Files\Microsoft Office\Office 15\XLSTART
  
  Office 2013 C2R – C:\Program Files\Microsoft Office 15\root\Office 15\XLSTART

Note: If your workbook or template contains a macro that automatically runs when the workbook is opened, such as Autpen. By default that macro will also run when you start Excel.

 

[manjit_b]

Hey, was there y solution to this?

 

[Worf]

Welcome

If you apply conditional formatting, do you get the correct colors?