Color Management In Excel 2003 Through 2013
Colors can play an important part in Excel applications. For example, clients will choose to use certain colors to indicate the status of a job, color coding sales rep lines on a report, and so on. As a developer, sometimes you need to copy the color of a cell to another cell, or to just assign a cell a color.
Excel 2010/2013 allow three "types" of colors:
(1) Standard Colors (These are fixed and don't change if themes change)
(2) Theme Colors
(These change with the theme selected)
(3) Custom Colors (These are fixed and don't change if themes change)
Excel 2003 used Microsoft's first color method: Pallets for standard fill colors. These fill colors are assigned to the "bucket" tool in the toolbar. The user can change the pallet color by assigning a different color to a pallet location. In Excel 2007 and beyond, Microsoft added Theme colors. All Excel versions can use the .Interior.Color attribute and this is the most compatible between versions. Attempting to copy a theme color using VBA variables from a cell that is not a theme color in Excel 2010 will result in an error. The code below shows examples of copying and assigning colors in different versions.
Also, theme colors can create issues for users that want to use a color that never changes. For example, let's say our user uses theme colors to assign a Green, Yellow and Red color that have specific meanings. If the user changes the theme in the Page Layout section, the colors can change and become meaningless. One solution for this issue is to convert the Theme colors to standard colors which will never change, even if the user selects a different set of Theme colors. The code below also shows how to convert theme colors to standard colors that will not change when the user selects a different theme.
Program Code
Option Explicit
Public Sub ConvertThemeToNonThemeColors()
' ****************************************************************
' The User Must Select All The Colors To Be Converted
' And Then Run This Procedure
' ****************************************************************
' This Procedure Converts Theme Colors to Standard Colors
' Using a Temporary Worksheet. The Temporary Worksheet
' Is Deleted After Colors Are Converted
' ****************************************************************
Dim wkbMyWorkbook As Workbook
Dim wksColorSheet As Worksheet
Dim wksTempSheet As Worksheet
Set wkbMyWorkbook = ThisWorkbook
Set wksColorSheet = ActiveSheet
Application.ScreenUpdating = False
wkbMyWorkbook.Sheets.Add After:=Sheets(Sheets.Count)
Set wksTempSheet = wkbMyWorkbook.Sheets(Sheets.Count)
wksColorSheet.Activate
Dim C As Range
For Each C In Selection
wksTempSheet.Cells(1, 1).Interior.Color = C.Interior.Color
C.Interior.Color = wksTempSheet.Cells(1, 1).Interior.Color
Next C
Application.DisplayAlerts = False
wksTempSheet.Delete
Application.DisplayAlerts = True
wksColorSheet.Activate
Application.ScreenUpdating = True
MsgBox ("Colors Converted From Theme to Standard")
End Sub
_____________________________________________________________________________________
Sub RemoveAllColorButLeaveCellBorders()
' ************************************************************
' The Following Applies To Theme, Standard and Customer Colors
' This Code Will Remove All Color and Patterns From A Cell But
' Leave Cell Borders
' ************************************************************
With ActiveCell.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End Sub
_____________________________________________________________________________________
Sub SetAStandardOrCustomColor()
' ************************************************************
' Set A Standard Or Custom Color Using RGB Function
' Note: These Colors will NOT change if the user
' changes the Theme color in the Page Layout section
' Note: This code will also change a Theme color to a
' standard color, setting .ThemeColor and .TintAndShade
' to Zero
' ************************************************************
ActiveCell.Interior.Color = RGB(255, 0, 0) 'Red
ActiveCell.Interior.Color = RGB(0, 255, 0) 'Green
ActiveCell.Interior.Color = RGB(0, 0, 255) 'Blue
ActiveCell.Interior.Color = RGB(255, 255, 255) 'White
ActiveCell.Interior.Color = RGB(0, 0, 0) 'Black
End Sub
_____________________________________________________________________________________
Sub SetAThemeColor()
' ************************************************************
' Set A Theme Color
' Note: These Colors WILL change if the user
' changes the Theme color in the Page Layout section
' ************************************************************
With ActiveCell.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorLight2
.TintAndShade = 0.799981688894314
.PatternTintAndShade = 0
End With
End Sub
_____________________________________________________________________________________
Option Explicit
Public Sub ColorProcessingExamples()
Dim lngThemeColor As Long
Dim dblTintAndShade As Double
Dim dblColor As Double
' ********************************************************
' C O L O R P R O P E R T Y M E T H O D
' The Simple Way To Assign A Cell A Color
' Compatible with Excel 2003 - 2013
' Assign Cells(1,2) the Color of Cells(1,1)
' ********************************************************
dblColor = ActiveSheet.Cells(1, 1).Interior.Color
ActiveSheet.Cells(1, 2).Interior.Color = dblColor
' ********************************************************
' An Alternate Way To Do The Same As Above
' ********************************************************
ActiveSheet.Cells(2, 2).Interior.Color = ActiveSheet.Cells(2, 1).Interior.Color
' ********************************************************
' C O L O R P A L L E T M E T H O D
' Assign Colors To A Cell Using the Pallet Number
' Note That the 35 and 45 below are Pallet Indexes
' This Reassigns a New Color To The Pallet Index
' And Then Uses the Updated Pallet Index To Assign
' the color to a Cell.
' ********************************************************
With ActiveWorkbook
.ResetColors 'Return Colors To the Default
.Colors(35) = RGB(143, 255, 143) 'Modify The Color Assigned To Pallet Index 35
.Colors(45) = RGB(255, 196, 80) 'Modify the Color Assigned to Pallet Index 45
End With
' ******************************************************
' Now Use The Reassigned Pallet Index To Color the Cell
' ******************************************************
ActiveSheet.Cells(3, 1).Interior.ColorIndex = 35
ActiveSheet.Cells(3, 2).Interior.ColorIndex = 45
' ******************************************************
' Theme Colors Are Not Available in Excel 2003 or Less
' ******************************************************
If Application.Version <= 11.5 Then
Exit Sub
End If
' ********************************************************
' T H E M E C O L O R M E T H O D
' Excel 2007, 2010 and 2013
' Note: This will fail if the source color is not a theme
' color. Theme colors can change if the user
' changes a theme, but the above two methods
' will not change since they are not theme colors.
'
' The procedure below copies theme colors from one cell
' to another.
' ********************************************************
lngThemeColor = ActiveSheet.Cells(4, 1).Interior.ThemeColor
dblTintAndShade = ActiveSheet.Cells(4, 1).Interior.TintAndShade
ActiveSheet.Cells(4, 2).Interior.ThemeColor = lngThemeColor
ActiveSheet.Cells(4, 2).Interior.TintAndShade = dblTintAndShade
End Sub