Quick tip: in the Excel palette, there are two rows of colors that are rarely used and can usually be set to custom values without visible changes on sheets of other nations.
Here's the code to create a reasonable set of "soft tone" colors that are much less offensive than the default values:
Public Sub SetPalePalette (optional wbk like Excel.Workbook) "This routine creates a custom palette of pale tones that you can use for controls, headers and dialogs
' ** THIS CODE IN THE PUBLIC DOMAIN ** ' Nigel Heffernan http://Excellerando.Blogspot.com
'The Excel color palette has two hidden lines that are rarely used: Line 1: colors from 17 to 24 "Line 2: colors from 25 to 32 - used in SetGrayPalette in this book
The 'code to fix the existing screen update setting and, if necessary, “temporarily suspend the update until this procedure is annoying” flickers on the screen ... and restores the screen update when exiting, if necessary.
Dim bScreenUpdating As Boolean
bScreenUpdating = Application.ScreenUpdating
If bScreenUpdating = True then Application .ScreenUpdating = False End If
'If Application.ScreenUpdating <> bScreenUpdating Then' Application.ScreenUpdating = bScreenUpdating 'End If
If wbk means nothing Set wbk = ThisWorkbook End If
With wbk
.Colors(17) = &HFFFFD0 ' pale cyan .Colors(18) = &HD8FFD8 ' pale green. .Colors(19) = &HD0FFFF ' pale yellow .Colors(20) = &HC8E8FF ' pale orange .Colors(21) = &HDBDBFF ' pale pink .Colors(22) = &HFFE0FF ' pale magenta .Colors(23) = &HFFE8E8 ' lavender .Colors(24) = &HFFF0F0 ' paler lavender
End with
If Application.ScreenUpdating <> bScreenUpdating Then Application.ScreenUpdating = bScreenUpdating End If
End Sub
Public Sub SetGreyPalette () 'This routine creates a custom greyshades palette that you can use for controls, headers, and dialogs
' ** THIS CODE IN THE PUBLIC DOMAIN ** ' Nigel Heffernan http://Excellerando.Blogspot.com
'The Excel color palette has two hidden lines that are rarely used: “Line 1: colors from 17 to 24” - used in SetPalePalette in this book' Line 2: colors from 25 to 32
'The code to fix the existing screen update settings and, if necessary,' temporarily suspend the update until this procedure is annoying 'flickers on the screen ... do not forget to restore the screen update when exiting!
Dim bScreenUpdating As Boolean
bScreenUpdating = Application.ScreenUpdating
If bScreenUpdating = True then Application .ScreenUpdating = False End If
'If Application.ScreenUpdating <> bScreenUpdating Then' Application.ScreenUpdating = bScreenUpdating 'End If
With this book .Colors (25) = & HF0F0F0 .Colors (26) = & HE8E8E8 .Colors (27) = & HE0E0E0 .Colors (28) = & HD8D8D8 .Colors (29) = & HD0D0D0 .Colors (30) = & HC8C8C8 '& HC0C0C0' Scipped & HC0C0C0 is the usual 25% gray color in the main palette. Colors (31) = & HB8B8B8 'Note that tears become wider: the human eye is more sensitive. Colors (32) = & HA8A8A8' to changes in bright sulfur, so this will be perceived as a linear scale. End with
'In the right column of the Excel palette, the following gray pages are set by default:
'Colors (56) = & H333333' Colors (16) = & H808080 'Colors (48) = & H969696' Colors (15) = & HC0C0C0 'default '25% gray'
'This should be changed to improve the color of the “gap” and make the colors easily distinguishable
With this book .Flowers (56) = & H505050 .Flowers (16) = & H707070 .Flowers (48) = & H989898 '. Colors (15) = & HC0C0C0 End with
If Application.ScreenUpdating <> bScreenUpdating Then Application.ScreenUpdating = bScreenUpdating End If
End Sub
You can write the CaptureColors and ReinstateColors functions for each Open () and BeforeClose () event workbook ... Or even for each sheet, activate and deactivate the event.
I have a code lying somewhere that creates a “thermal” color gradient for three-dimensional diagrams, giving you progress from “cold” blue to “hot” red in thirty-two steps. This is more complicated than you might think: the gradient of colors that will be perceived by the human visual system at “equal intervals” (which works on a logarithmic intensity scale and has non-linear scales for red, green and blue colors as “strong” colors) takes time to build - and you need to use VBA to force MS Chart to use the colors you specified in the order you specified.