Excel macro: color-code based on input, formula, or reference (blue, black, green)

Question

Hello,

Can you help me create or input a macro that will color-code cells in excel according to the following color-scheme (common in financial models)?

Blue - Hard-coded values (text or numbers)

Black - Formulas (example: =VLOOKUP(, =IF(, =OR(, etc.)

Green - References from another worksheet (example: =Sheet2!E15)

I would like to be able to select a range of cells, and then click a macro button on the toolbar that formats the cells in this way.  I am not very familiar with macros or visual basic.

Thank you very much for your help.

Ryan

Answer 1

I'm assuming that you want the font color changed as to opposed to a cell highlight (i.e. fill). A cell with a black fill would require a font color change as well.

Tap Alt+F11 and when the VBE opens, immediately use hte pull-down menu to Insert, Module. Paste the following into the new pane titled something like Book1 - Module1 (Code),

Sub mcrFinancial_Color_Codes()

Dim rng As Range, rErr As Range

On Error Resume Next

For Each rng In Intersect(ActiveSheet.UsedRange, Selection)

If rng.HasFormula Then

Set rErr = Range(Mid(rng.Formula, 2, Len(rng.Formula) - 1))

If CBool(Err) Then

rng.Font.ColorIndex = 1 'black

Else

rng.Font.ColorIndex = 4 'green

End If

Err = 0

ElseIf CBool(Len(rng.Value)) Then

rng.Font.ColorIndex = 5 'blue

Else

rng.Font.ColorIndex = xlAutomatic 'default

End If

Next rng

Set rErr = Nothing

End Sub

Tap Alt+Q to return to your worksheet. Select a range of cells and tap Alt+F8 to Run the macro. You also can tap Alt+F8 and use Options to assign a shortcut key combination.

Here is an image of an Excel table demonstrating the basic ColorIndex colors:

Answer 2

This is extremely helpful--absolutely perfect.  Thank you very much.

2 follow-up questions:

1. Is there a way to make this formatting dynamic--so that if I change a hard-code to a formula, it will automatically change colors without the need to apply the macro again?
2. Is there a way to modify this code so that if a value is hard-coded, the cell will receive a yellow-fill, and if the value is not hard-coded (formula, reference, etc.), it will not receive a fill - and to make this dynamic?

Thanks very much.

Answer 3

Tap Alt+F11 and in the same module sheet, paste the following User Defined Function (i.e. UDF) code,

Public Function fcnFinancial_Color_Codes(ByRef rng As Range) As Integer

Dim rErr As Range

fcnFinancial_Color_Codes = 0

On Error Resume Next

If rng.HasFormula Then

Set rErr = Range(Mid(rng.Formula, 2, Len(rng.Formula) - 1))

If CBool(Err) Then

fcnFinancial_Color_Codes = 1  'formula

Else

fcnFinancial_Color_Codes = 2  'reference

End If

Err = 0

ElseIf CBool(Len(rng.Value)) Then

fcnFinancial_Color_Codes = 4      'value

End If

Set rErr = Nothing

End Function

Tap Alt+Q to return to your worksheet. You can now use this UDF in conditional formatting. Select a range (e.g. D2:D99), noting the active cell (e.g. D2) and create a new Conditional Formatting Rule based upon a formula and use the following for Format values where this formula is true:,

=fcnFinancial_Color_Codes(D2)=4

Click Format and set a yellow Fill and a blue font. Click OK to accept the formatting and then OK to create the new rule. That should set the typed values as blue text with a yellow fill. Repeat for two more new CF rules with =fcnFinancial_Color_Codes(D2)=1 and =fcnFinancial_Color_Codes(D2)=2, setting the CF format as appropriate for each.

Answer 4

Hi Jeeped,

Thank you for the original post, it's been incredibly helpful! I typically color cells red when they are linked from exterior files and green when they are linked from a sheet within the same file. I noticed the current version color`s the cells green under both scenarios. As well, I only prefer coloring numbers and not text.

Unfortunately, I`m not sure how to manipulate the current code to reflect this. Therefore, I was wondering if you could provide a revised version reflecting (1) the red and green differentiation and (2) coloring for only numbers.

Much appreciated!

Virinder

Answer 5

@Virinder;

Thank you for your kind words. As this thread has already been rewritten once for a UDF version of the original macro, could you start a new thread with your requirements? I believe that is the best way for your question to receive the focus it deserves.