Colour unique formulae and constant cells of all sheets of activeworkbook
来源:互联网 发布:微观经济数据 编辑:程序博客网 时间:2024/06/10 14:48
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
- Colour unique formulae and constant cells of all sheets of activeworkbook
- 色彩构成(Interaction of Colour)
- aspose cells Setting Display Formats of Numbers and Dates
- A Survey Of Methods For Colour Image Indexing And Retrieval In Image Databases
- Testing and the Role of ALL MEMBER
- 12 most desirable skills in all of IT and all of information technology!
- 通过ActiveWorkbook.Sheets.Add将新表插入到最后的代码
- Use of undefined constant OCI_COMMIT_ON_SUCCESS
- Notice: Use of undefined constant
- Use of undefined constant FTP_BINARY
- Use of undefined constant ws1
- 【MonogDB】The description of index(三) Compose and unique Index
- vbs : Format a Range of Cells
- 4 the cells of the nervous system
- Unique features of OpenVZ
- An old post of mine for Leo and all newbies
- Display a Directory Tree of all Subdirectories and Files
- vim separate with . and replace all to end of line
- 网线接发
- 试验网站#1搜索引擎优化收录情况记录(断续运行)2007-12-6
- 新一轮股票行情可以进入了,但是很可能是在双头的第一个底部。
- 试验网站#3搜索引擎优化收录情况记录2007-12-6
- 国际化软件开发与测试模型研究与实践(great!)
- Colour unique formulae and constant cells of all sheets of activeworkbook
- 使用Java RTP传输声音和视频的程序(绝对经典)
- Eclipse SVN plugin安装
- 4.6 界面友好的启动程序(一)
- Can the msgbox vbYesNo button captions be changed in VBA?
- User Interface Freeze Milestone
- CSS div float
- 验证码 完整代码
- xml read check