Logicwurks Home Page

Links To Excel Code Examples

Range/Wkb/Wks Variables
Add Grand Totals Using Ranges
Using Range Offset Property
Using Range Find Method
Union Of Ranges
Parse Range Strings
Delete Duplicate Rows
Delete Rows And Columns
Worksheet Variables
TypeName And TypeOf
Loop Through Worksheets
Loop Through Open Workbooks
Form Button Magic
Command Button Magic
Add Worksheets Dynamically
Find Last Row Or Column
Copy And Paste Special
Copy To Specific Cell Types
Range Copy With Filter
Open An Excel File
Open An Excel File w/Params
Open An Excel File On Web
Save A Workbook
Clone A Workbook
Test If WEB URL Exists
Parse Using Split Command
Color Management
Convert Cell Color To RGB
Sort Methods 2003 - 2010
Sort Alpha/Numeric In ASCII
Search Using Match Function
Search Using Vlookup Function
Using Find Instead of Vlookup
Remove String Non-Printables
Auto_Open And Auto_Close
Initialize Form At Open
Edit Numerics In UserForm
Load Combo And List Boxes
Floating Sheet Combo Boxes
Advanced User Form Coding
Excel Events
Worksheet Change Events
Binary Search Of Array
Typecast Constants
Excel Error Handling
Handling Optional Parameters
Data Validation Drop Downs
Read A Text File w/Handle
Write A Text File w/Handle
Read A Text Fiile w/Script
Text File Processing Examples
Test For Exists Or Open
Splash Screen
Dynamically Load Formulas
Date Examples
Date Find Same Days
Convert Month To Number
Initialize Arrays
Load Arrays Using Evaluate
Redim An Array
Reassign Button Action
Timer Functions
Legacy Calendar Control
Excel 2010 Date Picker
Date Picker Alternative
Generate Multiple Worksheets
Read Access Data Into Excel
Send Outlook Email w/Attach
Copy AutoFilters To Sheets
Export A Text File
Get Windows User Name
VBA Format Statement
Manipulate Files via VBA
Dynamically Load Images
Loop Through Worksheet Objects
Loop Through Form Objects
Loop Through Files with DIR
Active-X Checkboxes
Add Forms Checkboxes Dynam
Paste Pictures Into Excel
Copy Pictures Sheet To Sheet
Copy Pictures Sheet To Sheet
Create Forms Buttons With VBA
Extract Filename From Path
Convert R1C1 Format to A1
Special Cells Property
Insert Cell Comments

Links To Access Code Examples

Create Recordset With AddNew
Multi-Select List Boxes
Update Field(s) In A Recordset
Update Excel Pivot From Access
Import A Tab Delimited File
Export Excel FileDialog
Create Excel Within Access
Open Excel Within Access
Open Excel OBJ From Access
Format Excel From Access
Import Tab Delim w/WinAPI
Initialize Global Variables
Using TempVars For Globals
Access Error Handling
Loop Through Form Controls
Insert A Calendar Control
Create A Filtered Recordset
Populate Combo Boxes
Bookmarks And Forms
Combo Box Multiple Sources
Passing Form Objects
Create VBA SQL Statements
Create Dynamic Queries
Display File Images On A Form
Manipulate Files via VBA
Manipulate Files via Scripting
Number Subform Records
Reference Subform Objects
Parse Delimited Fields
Parameterized Queries (VBA)
Manipulating QueryDefs In VBA
FindFirst On Combined Keys
Dlookup Command
Dlookup In Form Datasheet
Execute SQL Delete Records
Commit Form To Table
Report With No Data
Reference Form Objects
DSNLess Connections To MySQL
Print Active Form Record
Count Records in Linked Tables
Delete Empty Tables
Open Linked SQL Tables


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)


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
Application.DisplayAlerts = True

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