Makro anpassen

Hallo,
ich habe das Makro im Forum gefunden.


Private Sub Rahmen_einfügen()
'Wenn Zelle in Spalte A gefüllt, dann Rahmen über bedingte Formatierung einfügen
Dim Wert1 As String
Dim Wert2 As String
Wert1 = InputBox("bitte Zelle eingeben, mit der begonnen werden soll", "erster Wert", "A1")
Wert2 = InputBox("bitte Zelle eingeben, mit der Rahmen enden soll", "zweiter Wert", "N20000")
Range(Wert1 & ":" & Wert2).Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$A1"""""
With Selection.FormatConditions(1).Borders(xlLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.FormatConditions(1).Borders(xlRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.FormatConditions(1).Borders(xlTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.FormatConditions(1).Borders(xlBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range("a1").Select
End Sub

Das Makro passt aber nicht. Beginn soll immer A1 sein und Ende (sowohl Zeile / Spalte) immer die letzte gefüllte Zelle.
Ich kenne mich damit aber nicht aus. Kann mir jemand schnell helfen?
Vielen Dank!
Silke

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Benutzer

Sub Rahmen_einfügen() Dim rng As Range Set rng = ActiveSheet.UsedRange rng.FormatConditions.Delete rng.FormatConditions.Add Type:=xlExpression, Formula1:="=$A1""""" With rng.FormatConditions(1).Borders(xlLeft) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With rng.FormatConditions(1).Borders(xlRight) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With rng.FormatConditions(1).Borders(xlTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With rng.FormatConditions(1).Borders(xlBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With End Sub

Option Explicit Sub Klappt() Dim N As Byte With Worksheets("Tabelle1").UsedRange For N = 7 To 10 With .Borders(N) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With Next N End With Range("a1").Select End Sub Sub Klappt_Nicht() Dim N As Byte With Worksheets("Tabelle1").UsedRange .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, Formula1:="=$A1""""" For N = 7 To 10 With .FormatConditions(1).Borders(N) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With Next N End With Range("a1").Select End Sub

Sub Klappt_Jetzt_Auch() Dim N As Byte With Worksheets("Tabelle1").UsedRange .Parent.Range("A1").Select .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, Formula1:="=$A1""""" For N = 1 To 4 With .FormatConditions(1).Borders(N) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With Next N End With End Sub

Private Sub Rahmen_einfügen() Dim aktWS As Worksheet, i%, iCalc% Set aktWS = ActiveSheet With Application iCalc = .Calculation .ScreenUpdating = False .EnableEvents = False .Calculation = xlCalculationManual 'Tabelle anpassen !!!!!!!!!!!!!!! With Sheets("Tabelle1") .Select .Cells.FormatConditions.Delete With .UsedRange With .FormatConditions.Add(Type:=xlExpression, _ Formula1:="=$A" & ActiveCell.Row & """""") For i = 1 To 4 With .Borders(i) .LineStyle = xlContinuous .TintAndShade = 0 .Weight = xlThin End With Next i End With End With End With aktWS.Select .EnableEvents = True .Calculation = iCalc .ScreenUpdating = True End With End Sub

Beliebteste Forumthreads (12 Monate)

6
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Links zu Excel-Dialogen

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

Makro anpassen

6 Beiträge zum Forumthread Beiträge zu diesem Forumthread

VBA Beispiele zu verwandten Themen

Links zu Excel-Dialogen

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

6
Beiträge zum Forumthread

Beiträge zu diesem Forumthread