Change color based on cell content, font color, or fill color
Change color in a selected range, the active tab, or the active workbook
Set default and routine color numbers
Exclude cells from evaluation
Demos and Software
Overview
Watch this video for an overview of Excel Color Tools
9:40
Color Tools
Ribbon Interface
Font Color
Change font color based on cell content or a combination of cell content and font color.
Cell Content
Change the font color based on cell content. Cell content is evaluated as one of nine possibilities. These are number, date, formula, text, mixed, error, empty, formula contains, or value contains. The user can change the font color of each of these data types individually, or change constants, numbers and dates, or formulas and text at the same time. The font color of all cell content, or any combination of cell content, can be changed using the User Choice option. The SSRB Recommended option changes font color for all constants, formulas, and mixed cell content.
Find
Identifies and provides the user an option to select the first cell it finds that contains the font color selected or input.
Font Color
Change the font color of all cells that contain a specified color, or change only the cells that contain the specified color and are evaluated as a number, date, formula, text, mixed, error, or empty. The user can change the font color of each of these data types individually, or change constants, numbers and dates, or formulas and text at the same time.
List
Lists the unique font color utilized in the selected range or the active tab. This option provides the user an easy method to determine the color numbers associated with multiple font colors.
Count
Counts the number of cells which contain the font color selected or input.
Fill Color
Change fill color based on cell content or a combination of cell content and fill color.
Cell Content
Change the fill color based on cell content. Cell content is evaluated as one of nine possibilities. These are number, date, formula, text, mixed, error, empty, formula contains, or value contains. The user can change the fill color of each of these data types individually, or change constants, numbers and dates, or formulas and text at the same time. The fill color of all cell content, or any combination of cell content, can be changed using the User Choice option. The SSRB Recommended option changes fill color for all constants, formulas, and mixed cell content.
Find
Identifies and provides the user an option to select the first cell it finds that contains the fill color selected or input.
Fill Color
Change the fill color based on the fill color of the cells content and/or the evaluation of the type of data included in the cell. The user can elect to change the fill color of all cells that contain a specified color, or change only the cells that contain the specified color and are evaluated as a number, date, formula, text, mixed, error, or empty. The user can change the fill color of each of these data types individually, or change constants, numbers and dates, or formulas and text at the same time.
List
Lists the unique fill color utilized in the selected range or the active tab. This option provides the user an easy method to determine the color numbers associated with multiple fill colors.
Count
Counts the number of cells which contain the fill color selected or input.
Find, Count, List Color
3:59
content contains
4:38
Errors
Search for errors, count cells containing an error, or list all cells with an error.
Find
Selects the first cell within the selection or active tab that contains an error. The user can select a specific error type (#DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF, #VALUE!) or find any type of error. The user can elect to find the text equivalent of an error.
List
Lists the cell reference, error type, and formula of errors in the selected range or the active tab. The user can select any error or specific error types, and can elect to include the text equivalent of errors.
Count
Counts the cells within the selection or active tab that contain an error, by error type (#DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF, #VALUE!). The user can elect to count the text equivalent of an error.
Errors
4:35
Defaults
Set default color numbers, routine color numbers, color descriptions, and more.
The defaults option allows the user to:
Set default color numbers and routine color numbers in order to streamline the process in instances the same color numbers are used repeatedly, or simply wants quick access to color numbers used on a frequent basis.
Set the color numbers used by the Users Choice and SSRB Recommended macros.
Establish default variables utilized by the formula contains and value contains options.
Set Bypass Variables which are constants ignored for cell content evaluation.
Set the description of each color number.
Bypass Variables
3:32
Undo
Undo font color or fill color changes.
Undo allows the user to undo font color and fill color changes associated with one of the previous seven (7) change macros processed. The user must elect to enable or disable the capture of undo data. Undo options include the ability to clear undo data, set the maximum number of cells to capture undo data, and save undo data.
Enabled – font and fill color number information captured allowing the user to undo color changes.
Disabled – undo functionality disabled.
Backup
Generate a backup copy of the active workbook.
Backup
Generates a backup of the active workbook. The backup spreadsheet captures the current status of the active workbook, and is saved in the same directory as the active workbook. The backup file name is the name of the active workbook followed by a date and time stamp. Backup supports file types “xls”, “xlsx”, “xlsb”, and “xlsm”.
Process
Designate which cells are reviewed.
The process election allows the user to establish which cells within a selected range or the active tab will be evaluated.
All Cells
All cells will be evaluated.
Unlocked Cells
Only cells which are unlocked using Excels Lock Cell feature will be evaluated.
Locked Cells
Only cells which are locked using Excels Lock Cell feature will be evaluated.
Process
2:43
Lock Cell
Identify or count locked or unlocked cells.
Find
Finds the first locked or unlocked cell in the selection or active tab, and provides an option to select the cell.
Count
Counts the number of locked and unlocked cells in the selection or active tab.
Toggle
Allows the user an option on how to handle mixed cell content.
The toggle election specifies how to handle cell content which contains a formula that includes a constant (number, date, or text).
Mixed
Cell content with a combination of formula and constant evaluate as mixed, with two exceptions. Formulas which end with a comma followed by either a zero ",0)" or a one ",1)" are considered a formula. A formula which includes a constant variable contained in quotes evaluates as mixed.
Number
Formulas that include a hard-coded number variable evaluate as a number, with one exception. Formulas which end with a comma followed by a zero ",0)" are considered a formula. Cells with mixed cell content generally evaluate as a number, date, or formula.
Toggle
3:47
Color Number
Determine the font color number, fill color number, and other details associated with a cell.
The Excel Color Tools macros require either a font color number or fill color number associated with or to be applied to the cell content being evaluated.
Font
Color number for a specific cell.
Cell Info
Each cell in a spreadsheet has multiple individual characteristics. The content of each cell is represented by a formula and value. The formula can be specifically evaluated as a number, date, formula, text, mixed, error, or empty. The cell will also have a specific font, font size, font color, fill color, and may be locked and have conditional formatting applied. The Cell Info macro provides the details of each of these characteristics.
Fill
Color number for a specific cell.
Excel Buttons
Standard Excel options that are associated with color or facilitate functionality within Excel Color Tools.
These buttons are included on the Color Tools ribbon so they are easily accessible. The buttons are Theme Colors, Lock Cell, Automatic [Auto Calc], Format Painter, Fill Color, and Font Color.
A consistent color scheme in a spreadsheet greatly enhances and streamlines a user’s ability to understand the data presented.
The Excel Color Tools allows a user to easily apply a color scheme, or apply color to specific components of an Excel spreadsheet. The user can perform the following tasks based on and associated with color.
Designate variables to bypass during cell evaluation
Quickly identify data input by a user
Save a backup of the active workbook
Undo color changes
Find, count, or list font color or fill color
Find, count, or list errors
Easily manage a consistent color scheme
Easily identify cells with constants in the cell formula
The use of color and the application of a color scheme in a spreadsheet allows a user the ability to identify at a glance which data is a hard-coded constant (number, date, or text), which data is a formula, and which cells have mixed content (constant combined with a formula).
Realistically, we will encounter spreadsheets developed by ourselves or others that do not have a color scheme applied.
We may even have spreadsheets that adhere to a color scheme for the most part, but as time has passed, we can’t be confident the spreadsheet completely adheres to the scheme. Excel Color Tools makes it easy to apply or validate a color scheme, and includes options that allow the user to fine tune how specific cells, or specific variables within cell content are handled.
Excel Color Tools will benefit every Excel user.
