VBA Custom ColorIndex

Question

Is it possible to create a custom ColorIndex for VBA in Excel?

The workbook I'm working on has an excessive number of conditional formatting rules. I'm trying to convert those rules to a VBA script since the users don't how to "Paste As Value" and resist my attempts to teach them new Excel skills. So I'm updating the workbook to use VBA for much of the formatting and data validation. This workbook is replaced annually and I use the creation of the new workbook to make things a little bit better for everyone.

I know there is already a default ColorIndex which can be reference by VBA. But I would like to customize it to the color coding system we have developed over the last several years.

As I said there are an excessive number of Conditional Formatting rules, between 40 and 50, with most pertaining to the fill/interior color of the cell. I would rather not have to type out the RGB for each color over and over. But the default ColorIndex does not overlap the color coding we have developed, and, if I try to change too much in one go, my users will refuse to use the new workbook until I revert to the old method of formatting and data validation.

Answer 1

Here's one approach:

Add a new code module, name it (eg) Colors, and declare a bunch of constants to represent the colors you want to use for formatting:

Option Explicit

Public Const LightGrey As Long = 13684944
Public Const DarkGrey As Long = 5855577
'etc etc

Then in the rest of your code you can use those in place of the palette indexes, or instead of using RGB. Added bonus is you get a nice pop-up/intellisense: