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)
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
.Borders.LineStyle = xlNone
End If
End If
End With
Application.ScreenUpdating = True
End Sub
Public Function Last_R() As Long
With ActiveSheet
Last_R = .Cells.Find(What:="*", After:=Range("A1"), _
SearchOrder:=xlByRows, _
End With
End Function
Public Function Last_C() As Long
With ActiveSheet
Last_C = .Cells.Find(What:="*", After:=Range("A1"), _
SearchOrder:=xlByColumns, _
End With
End Function

Sample 2003

Sample 2007

Keine Kommentare:

Kommentar veröffentlichen

Word - Kontrollkästchen (Formularsteuerelement) auslesen...

Aus allen Worddateien sollen die Kontrollkästchen (Formularsteuerelement) ausgelesen werden - Haken gesetzt oder nicht. Auch ein Textfeld (F...