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.

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.

Already Subscribed? Click HERE to navigate to the "Example Files" section

How Do I Modify This To Fit My Specific Needs?

Chances are this post did not give you the exact answer you were looking for. We all have different situations and it's impossible to account for every particular need one might have. That's why I want to share with you: My Guide to Getting the Solution to your Problems FAST! In this article, I explain the best strategies I have come up with over the years to getting quick answers to complex problems in Excel, PowerPoint, VBA, you name it

I highly recommend that you check this guide out before asking me or anyone else in the comments section to solve your specific problem. I can guarantee 9 times out of 10, one of my strategies will get you the answer(s) you are needing faster than it will take me to get back to you with a possible solution. I try my best to help everyone out, but sometimes I don't have time to fit everyone's questions in (there never seem to be quite enough hours in the day!).

I wish you the best of luck and I hope this tutorial gets you heading in the right direction!

Chris "Macro" Newman :)