PROBLEMS WITH EXCEL CUSTOM CONDITIONAL FORMATTING COLOR MENUS

Question

I cannot change colors in conditional formatting. I select a range of cells with number values in them. I click conditional formatting and the control window pops up fine. However, when I try to make my own format, when it comes to selecting colors, I click the dropdown to change colors and click the new color, but it doesn't change the color selection.  It’s as if the menu is malfunctioning in that my click selection doesn’t go through.

The problem is not always consistent.

[1] Sometimes I cannot make any color selection changes.

[2] Sometimes I can change colors but only in the first 4 columns of the 6 row by 10 column “Theme Colors” grid.

[3] Sometimes I can additionally change colors in the 1 row by 10 column “Recent Colors” grid.

[4] Sometimes I can additionally change colors in the “More Colors” advanced menu.

This problem only ever occurs on my home computer using excel, the same spreadsheet that produces this error on my home computer, functions without any problems on excel on my work computer.

Any suggestions on what may be the problem and how to remedy?

Answer 1

Hi PatrickMoran2,

I am Vijay, an Independent Advisor. I am here to work with you on this problem.

Please try these options one by one and check after each option.

1. Sometimes, add-ins can cause problems. To determine, if add-in is a problem start your application in safe mode and see if problem has gone away.

To start in Safe Mode -

Hold CTRL key and click on application icon and don't release the CTRL key till it asks you for Safe mode confirmation.

If yes, then start application normally and disable add-ins one by one and start application again every time you disable an add-in to determine the culprit add-in.

To disable add-in > File > Options > Add-ins > Down below you have Manage: Com Add-in : Press Go > Untick the Add-ins to disable and tick to enable them

2. Please look into C:\Users\User_Name\AppData\Roaming\Microsoft\Excel (replace user name with your user name, you can directly reach here by copy and pasting following without quotes in Explorer address bar - "%appdata%\Microsoft\Excel") and see if is contains few files / folders. Move those files to some other location (i.e create a backup of those files / folders and delete all files / folders from here). Hence, make Excel folder blank.

Now open Excel and see if the problem disappears or not.

Note - If the above path is not applicable for your installation, you need to find XLSTART path. Excel folder contains XLSTART folder in itself (In case, your XLSTART path is not in Appdata)

Open Excel > ALT+F11 > If Immediate Window is not visible, type CTRL+G. Same can be also be accessed through View > Immediate Window > Type "? application.StartupPath" without quotes and press enter > You will have your path below.

3. Try repairing your office installation (Quick repair is good enough). Try repairing your Office installation. First try Quick Repair (takes 5 - 10 mins) , if that doesn't help then try Online Repair (Takes more than 30 mins but less than 1 hour in general)

https://support.office.com/en-us/article/Repair...

Do let me know if you have any more question or require further help. If above didn't work, I will supply more troubleshooting steps to solve this problem.

Greetings,

The issue is with conditional formatting and turning borders on and off.  The software on my previous computer and the current computer had no issues. . .  Until. . .  The previous computer used the Excel file for years, no issue.  The three week old computer operated the Excel file without issue until last night.  The software simple does not control the conditional format used to control the borders.  Please take note, all was working on both computers and other computers and has been working for years until now.

Trouble Shooting steps:

Verified .xlsm

Checked different (similar) files

Works on other computers (so far no other complaints)

Repaired and Online repaired Office 365

Attempted all steps from above.

Answer 2

Hi PatrickMoran2,

I am Vijay, an Independent Advisor. I am here to work with you on this problem.

Please try these options one by one and check after each option.

1. Sometimes, add-ins can cause problems. To determine, if add-in is a problem start your application in safe mode and see if problem has gone away.

To start in Safe Mode -

Hold CTRL key and click on application icon and don't release the CTRL key till it asks you for Safe mode confirmation.

If yes, then start application normally and disable add-ins one by one and start application again every time you disable an add-in to determine the culprit add-in.

To disable add-in > File > Options > Add-ins > Down below you have Manage: Com Add-in : Press Go > Untick the Add-ins to disable and tick to enable them

2. Please look into C:\Users\User_Name\AppData\Roaming\Microsoft\Excel (replace user name with your user name, you can directly reach here by copy and pasting following without quotes in Explorer address bar - "%appdata%\Microsoft\Excel") and see if is contains few files / folders. Move those files to some other location (i.e create a backup of those files / folders and delete all files / folders from here). Hence, make Excel folder blank.

Now open Excel and see if the problem disappears or not.

Note - If the above path is not applicable for your installation, you need to find XLSTART path. Excel folder contains XLSTART folder in itself (In case, your XLSTART path is not in Appdata)

Open Excel > ALT+F11 > If Immediate Window is not visible, type CTRL+G. Same can be also be accessed through View > Immediate Window > Type "? application.StartupPath" without quotes and press enter > You will have your path below.

3. Try repairing your office installation (Quick repair is good enough). Try repairing your Office installation. First try Quick Repair (takes 5 - 10 mins) , if that doesn't help then try Online Repair (Takes more than 30 mins but less than 1 hour in general)

https://support.office.com/en-us/article/Repair...

Do let me know if you have any more question or require further help. If above didn't work, I will supply more troubleshooting steps to solve this problem.