Mark cells with formula!

Marked or all cells with formulas are represented with a red frame. The formulas are written additionally into the comment of the respective cell. Repeat the code deletes the frame and the comment. By the line "Option Private Module" the macros are not indicated in the macro window, but can be assigned however over the name to a button. The files at the end of the article are Excelfiles of the version 2003 and 2007.


The following code belonged In "Module1"

Option Explicit
Option Private Module
Public Sub Formula_Frame_Comment()
Dim rngRange As Range
Dim strTMP As String
On Error GoTo Fin
Application.ScreenUpdating = False
If Selection.Count > 1 Then
strTMP = Selection.Address(False, False)
Else
strTMP = Range(Cells(1, 1), _
Cells(Last_R, Last_C)).Address(False, False)
End If
For Each rngRange In Range(strTMP)
With rngRange
If .HasFormula Then
If .Comment Is Nothing Then
.Borders.LineStyle = xlDot
.Borders.Weight = xlThin
.Borders.ColorIndex = 3
.AddComment (.Formula)
.Comment.Shape.TextFrame.AutoSize = True
Else
.Borders.LineStyle = xlNone
.Comment.Delete
End If
End If
End With
Next
Fin:
Application.ScreenUpdating = True
End Sub
Public Function Last_R() As Long
With ActiveSheet
Last_R = .Cells.Find(What:="*", After:=Range("A1"), _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
End With
End Function
Public Function Last_C() As Long
With ActiveSheet
Last_C = .Cells.Find(What:="*", After:=Range("A1"), _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
End With
End Function


Sample 2003

Sample 2007

Kommentare

Beliebte Posts aus diesem Blog

Formeln - auch Array - per VBA eintragen...

Alle Dateien eines Ordners - Optional mit Unterordner

Excel -> Word in Textmarken (Bookmarks)...