Using Cell Formatting Logic With Excel Formulas
Wishful Thinking?
Have you ever had a time where you wished you could write an IF formula in Excel based on whether or not a cell was filled with the color green or not? Unfortunately, Excel doesn’t have a built-in “IsGreen” function you can call to perform such logic. However, with the power of VBA you can write your own functions and actually call them from the Formula Bar! This can be extremely powerful and extends Excel’s logical prowess to a near limitless level! Let’s take a look at how we can add our own functions into our spreadsheets.
Where Do I Write The Function Logic?
I recommend storing your custom functions inside a VBA module. Here are the steps to create a brand new module within your spreadsheet.
- Inside your spreadsheet use the keyboard shortcut Alt + F11 to open up the Visual Basic Editor window
- On the left-hand side of the window, you should see a Project pane that will display every Excel workbook/add-in you currently have open
- Find the Project Name that matches the Excel file you want to store the functions in and right-click on the Project Name
- Inside the menu that pops up, go to Insert -> Module
- A blank white canvas should appear on your screen and this is where you will want to write or paste in your custom function code
Examples of Functions You Can Add
This is where you can let your imagination run wild (especially if you have a good grasp on how to write VBA). But in case you are new to VBA (you can learn how to get started learning to write VBA here), I am going to provide you with a few common functions that might be useful when trying to logically analyze a cell’s formatting.
Function To Determine If Cell Text Is Bold
Function ISBOLD(cell As Range) As Boolean
'PURPOSE: Determine if cell has bold text or not
'SOURCE: www.TheSpreadsheetGuru.com
'Ensure function will recalculate after spreadsheet changes
Application.Volatile True
'Test Cell Value
On Error GoTo PartiallyBold
ISBOLD = cell.Font.Bold
On Error GoTo 0
Exit Function
'Handle if only part of the cell value is bolded
PartiallyBold:
If Err.Number = 94 Then ISBOLD = True
End FunctionFunction To Determine If Cell Text Is Italicized
Function ISITALIC(cell As Range) As Boolean
'PURPOSE: Determine if cell has italicized text or not
'SOURCE: www.TheSpreadsheetGuru.com
'Ensure function will recalculate after spreadsheet changes
Application.Volatile True
'Test Cell Value
On Error GoTo PartiallyItalic
ISITALIC = cell.Font.Italic
On Error GoTo 0
Exit Function
'Handle if only part of the cell value is italicized
PartiallyItalic:
If Err.Number = 94 Then ISITALIC = True
End FunctionFunction To Determine If Cell Text Is Underlined
Function ISUNDERLINED(cell As Range) As Boolean
'PURPOSE: Determine if cell has underlined text or not
'SOURCE: www.TheSpreadsheetGuru.com
'Ensure function will recalculate after spreadsheet changes
Application.Volatile True
'Test Cell Value
If cell.Font.Underline = xlNone Then
ISUNDERLINED = False
Else
ISUNDERLINED = True
End If
End FunctionFunction To Determine If Cell Text Is A Specific Color
Function ISFONTCOLOR(cell As Range, TargetColor As Range) As Boolean
'PURPOSE: Test if cell font color matches a specified cell fill color
'NOTE: Color changes will not trigger your spreadsheet to re-calculate
'SOURCE: www.TheSpreadsheetGuru.com
'Ensure function will recalculate after spreadsheet changes
Application.Volatile True
'Test Cell Font Color
If cell.Font.Color = TargetColor.Interior.Color Then
ISFONTCOLOR = True
Else
ISFONTCOLOR = False
End If
End FunctionFunction To Determine If Cell Fill Is A Specific Color
Function ISFILLCOLOR(cell As Range, TargetColor As Range) As Boolean
'PURPOSE: Test if cell fill color matches a specified cell fill color
'NOTE: Color changes will not trigger your spreadsheet to re-calculate
'SOURCE: www.TheSpreadsheetGuru.com
'Ensure function will recalculate after spreadsheet changes
Application.Volatile True
'Test Cell Fill Color
If cell.Interior.Color = TargetColor.Interior.Color Then
ISFILLCOLOR = True
Else
ISFILLCOLOR = False
End If
End FunctionCalling A Custom Function
Once you have written your function in the VBA module, you can then start to use it! Simply begin typing the function inside the formula bar and you will see your custom function appear as a viable selection. Note that you will not get any ScreenTips with your custom functions (come on Microsoft!), so you will need to memorize how to properly use your custom function.
Formula Recalculation
You will need to be careful with these formulas since changing the format of a cell does not trigger a spreadsheet recalculation. Always make sure you hit the F9 key before you begin your analysis while using a custom function that takes into account formatting in your spreadsheets.
Download An Example File With These Functions
To help you further, I have created an example workbook with all the custom functions described in this article put to use so you can see them in action.
If you would like to get a copy of the Excel file I used throughout this article, feel free to directly download the spreadsheet by clicking the download button below.
Learn my best Excel chart design hacks. These will turn your ugly charts into professionally crafted graphics, leaving your boss speechless!!!
Keep Learning
Chris Newman
Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.