Macro Request: Colour-Coding based on Input, Formula or Link

Question

Hi,

I'm looking to insert a macro in excel that would colour-code numbers based on the following requirements:

• Blue: hard-coded numbers
• Black: formulas (ex. sum, vlookup, average, etc)
• Green: number that is linked from another sheet within the same file
• Red: number that is linked from another sheet from an exterior file
• Colour-coding done upon data entry rather than re-running macro
• Only numbers would be colour-coded, all textual inputs remain in black

Thanks in advance,

Virinder

Answer 1

The macro mostly works; when I run it I get a run-time error (1004). When I click debug, the following part of the code is highlighted in yellow

For Each r In ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas)

Also, it doesn't auto color upon entry. I installed the code as an add-in and creating a shortcut (Ctrl + w) to run it, could this be causing the problem?

Thanks,

Virinder