×

# Using Cell Formatting Logic With Excel Formulas

By Chris Newman •  Updated: 06/30/15 •  6 min read

## 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.

1. Inside your spreadsheet use the keyboard shortcut Alt + F11 to open up the Visual Basic Editor window
2. 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
3. Find the Project Name that matches the Excel file you want to store the functions in and right-click on the Project Name
4. Inside the menu that pops up, go to Insert -> Module
5. 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

'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 Function``````

### Function To Determine If Cell Text Is Italicized

``````Function ISITALIC(cell As Range) As Boolean
'PURPOSE: Determine if cell has italicized text or not

'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 Function``````

### Function To Determine If Cell Text Is Underlined

``````Function ISUNDERLINED(cell As Range) As Boolean
'PURPOSE: Determine if cell has underlined text or not

'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 Function``````

### Function 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

'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 Function``````

### Function 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

'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 Function``````

### Calling 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.

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.