Need a macro to change font color based on cell formula

January 4, 2012 at 11:47:06
Specs: Windows 7
Excel Macro Change Font Color

I was looking for some help in a workbook. This workbook consists of multiple sheets. Each sheet contains data that I entered, formulas that add items on an individual sheet, links to other sheets and links to other workbooks.

I would like to write a macro that will change the color of the font in the entire workbook that depends on the formula or lack thereof in a cell. The font color is based on four scenarios:

1) If the cell contains text (such as the word "Average") or a hard-coded number (such as "150,000"), the font of the cell should be in blue.

2) If the cell contains a formula that calculates based on items on cells located in on the sheet where the formula is located, the font color should be black. For example on Sheet1, the font of the cell containing the formula =SUM(E4:E12) should be black.

3) If the cell contains a formula that calculates based on (a) cells located on the other sheets or (b) located on the other sheet plus the sheet where the formula is located, the font color should be green. For example on Sheet1, the font of the cell containing the formula =Sheet2!A1 or =Sheet2!A1+Sheet1!A1 should be green.

4) If the cell contains a formula that calculates based on items on cells located in an entirely different workbook, the font color should be red.

Any help that you could offer is greatly appreciated.


See More: Need a macro to change font color based on cell formula

Report •

#1
January 5, 2012 at 13:26:37
I am assuming that the default font color in your workbook is Black, so this code doesn't bother checking for formulas that reference the sheet that the formula is on. If the font is already black, why write code to set it to black?

Obviously you'll have to test the code in your workbook to see if it does what you want since here is no way for me to replicate your formulas, text or numbers.

It seemed to do what you asked for when I set up a test workbook.

I suggest that you try it in a backup copy of your workbook since macros can not be easily undone.

Sub ColorMyFonts()
'Loop through Sheets
   For shtNum = 1 To Sheets.Count
'Loop through cells
     For Each cell In UsedRange
'If Text or Number, Font is Blue
      If WorksheetFunction.IsText(cell) Or WorksheetFunction.IsNumber(cell) Then
        cell.Font.ColorIndex = 5
      End If
'If Formula, check for "[" to see if a workbook is referenced
'If no "[", check for "Sheet" to see if Sheet is refernced
       If cell.HasFormula Then
         myForm = cell.Formula
           If InStr(1, myForm, "[") > 0 Then
             cell.Font.ColorIndex = 3
           ElseIf InStr(1, myForm, "Sheet") > 0 Then
                  cell.Font.ColorIndex = 50
           End If
      End If
     Next
   Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
January 11, 2012 at 15:20:45
It has worked so far. Thanks for the help.

Report •
Related Solutions


Ask Question