Using Cell Formatting Logic With Excel Formulas

Excel Functions IsBold IsColor IsItalic IsUnderlined

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

Use A Custom Function IsBold In Formula Bar

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. As always, in order to download this example file you will need to be a subscriber of my free tips newsletter.  If you click the green button below you can quickly sign up and be emailed the password to gain access to the subscribers-only area of this website.

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

                                                       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 :)