Colour unique formulae and constant cells of all sheets of activeworkbook

最新推荐文章于 2025-06-18 17:21:47 发布

最新推荐文章于 2025-06-18 17:21:47 发布 · 74 阅读

文章标签：

#PHP

本文介绍了一段VBA宏代码，该宏能够自动将工作簿中的所有常量单元格标记为黄色，并将非复制型公式单元格标记为紫色。此宏不标记那些跨列或向下复制的公式，确保了数据源的清晰识别。

A question in http://www.mrexcel.com/board2/viewtopic.php?t=304750

I am looking for a macro which does two things:

1. colours all constants in a workbook as yellow.

2. colours all formulae with purple but I don't want the formulae which are copies to be coloured (i.e. the ones which are copied across columns or copied down in rows).

is there any way of doing this?

----------------------------------------------------

My answer:

Sub Addbackcolor()
Dim sh As Worksheet, d As Object, r As Range
Set d = CreateObject("scripting.dictionary")
For Each sh In Sheets
d.RemoveAll
For Each r In sh.Cells.SpecialCells(xlCellTypeFormulas, 23)
If Not d.exists(r.FormulaR1C1) Then d.Add r.FormulaR1C1, r.Address
Next
sh.Range(Join(d.items, ",")).Interior.Color = &HFF99CC
sh.Cells.SpecialCells(xlCellTypeConstants, 23).Interior.Color = vbYellow
Next
Set d = Nothing
End Sub