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.

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.

It has worked so far. Thanks for the help.

Ask Your Question

Weekly Poll

Are you able to work from home during the pandemic?

Discuss in The Lounge

Poll History