Format cell/text color via VBA and supercede manual cell formatting like Cond.Format does

Hello,

I have a worksheet with many hundreds of rows and about 40 columns wide. It currently has a lot of Conditional Formats on many cells on every row to control color of cell and color of text as info in input. In many cases there are multiple Conditional Formats in cells.

The multitude of conditional formats are now causing a real slow down when inserting or deleting rows or columns in the workbook. It currently takes over 60 seconds to do so. I deleted all conditional formatting in the workbook and things get speedy again.

I am trying out some code I found to emulate conditional formatting.

Please Login or Register  to view this content.

This code works quite well but my issue is the cell/text color can be manually changed afterwards.

Conditional Formatting supercedes all manual cell/text color formatting. Is it possible to achieve the same result with VBA?

Please note that users do need to manually set cell/text color for their own use and thus it is not an option to simply lock the sheet and disable cell formatting.

Thank you for any help.

TV

Hello Pierre,

Correct, currently if a cell meets a conditional format, the cell color or cell text will change according to the condition. Once a conditional format is applied, you are free to select a different color for cell/text, but you will not see this new manually selected color until the conditional format for the cell is no longer applied due to a value being changed.

We currently do lock our sheets but as mentioned, users still need to alter cell color for their own use, for example on cells that will never have any conditional formatting, so we can not lock out the ability for the user to manually change cell/text color.

Pierre, I'm not sure If I don't understand or if you are not understanding me.

We protect our sheets but users still need to format cells (apply color) so we can not lock out that option.

We use conditional formatting on some columns but there are so many conditional formats it slows down the insertion or deletion of a row or column (even when vba is used to do so).

To prevent the slow down I would like to apply code like the example in my first post instead of using conditional formatting. The drawback is that using the code does not prevent a user to alter cell/text color of a cell already altered by code. The beauty of conditional formatting is it that color applied to a cell via conditional formatting can not be changed by the used as long as the rule of the conditional format is met.

So I am looking for code that can possibly protect cell color even if a user tries to change the color.