vba: zelle oberhalb der aktiven zelle suchen

Question

Hallo
ch habe ein Problem und zwar möchte ich die Prozedur (von malschauen) siehe untenauf mehrere verschiedene Tabelen anwenden. In den Tabellen sind die Bereiche unterschiedlich.

Meine Idee ist ich bennene die Unterschiedkichen Bereiche in den veschiedenen Excel Tabellen mit dem gleichen Namen.

Meine Frage lautet wie kann ich das einfärben der Zellen mit derProzedur auf unterschiedliche Bereiche (d.H. mal mehr oder mal weniger Zeilen) anwenden?

Dim r As Range
For Each r In Range("a2:af2") ' erste Zeile der Tabelle
'("b1").CurrentRegion
If IsDate(r.Value) Then
Select Case Weekday(r.Value)
Case 1
Range(Cells(2, r.Column), Cells(17, r.Column)).Interior.Color = vbBlue
Range(Cells(2, r.Column), Cells(17, r.Column)).Font.Color = vbWhite
Case 7
Range(Cells(2, r.Column), Cells(17, r.Column)).Interior.Color = vbBlue
Range(Cells(2, r.Column), Cells(17, r.Column)).Font.Color = vbWhite
Case Else
End Select
End If
Next r



bye
malSchauen
ch habe ein neues Problem und zwar möchte ich die Prozedur auf mehrere verschiedene Tabelen anwenden. In den Tabellen sind die Bereiche unterschiedlich.

Meine Idee ist ich bennene die Unterschiedkichen Bereiche in den veschiedenen Excel Tabellen mit dem gleichen Namen.

Meine Frage lautet wie kann ich das einfärben der Zellen mit derProzedur auf unterschiedliche Bereiche (d.H. mal mehr oder mal weniger Zeilen) anwenden?

Answer 1

Hi,

mache noch eine Case-Anweisung für die Zeilenanzahl in Abhängigkeit vom Tabellennamen - nach diesem Prinzip:

Dim r As Range
Dim inZeile As Integer
With ActiveSheet
Select Case .Name
Case "Tabelle1"
inZeile = 17
Case "Tabelle2"
inZeile = 8
End Select
For Each r In raBereich
If IsDate(r.Value) Then
Select Case Weekday(r.Value)
Case 1
.Range(.Cells(2, r.Column), .Cells(inZeile, r.Column)).Interior.Color = vbBlue
.Range(.Cells(2, r.Column), .Cells(inZeile, r.Column)).Font.Color = vbWhite
Case 7
.Range(.Cells(2, r.Column), .Cells(inZeile, r.Column)).Interior.Color = vbBlue
.Range(.Cells(2, r.Column), .Cells(inZeile, r.Column)).Font.Color = vbWhite
End Select
End If
Next r
End With

Bis später,
Karin

Answer 2

Hallo Karin,

funktioniert so weit gut nur mein Problem ist möchte das auf sehr viele verschieden Dateien nutzen.

Meine Idee ist ich bennene die Unterschiedlichen Bereiche in den veschiedenen Excel Tabellen mit dem gleichen Namen, so das ich die Prozedur nur einmal mit dem Namen erstelle.

Diese Prozedur dann immer wieder aufrufen kann ohne das ich die Namen ändern muß.

Vieleicht hast du auch noch eiinen Tipp für mich.

Gruß Erwin

Answer 3

Hi,

Meines Wissens kann es einen benannten Bereich in einer Mappe (Excel-Datei) nur einmal geben. Soll heissen: benennst Du einen Bereich auf "Tabelle1" z.B. mit "A", dann kannst Du diesen Namen auf anderen Tabellen dieser Mappe nicht mehr verwenden. Du müsstest also in jeder Tabelle einer Mappe einen eindeutigen Bereichsnamen vergeben. Das dann für alle Deine Mappen (die gefärbt werden sollen) identisch. Das stelle ich mir nicht gerade praktikabel vor.
Ich hätte da zwei Ansätze, welche ich mir als Anwender vorstellen könnte:
- Anzahl der zu färbenden Zeilen (bzw. letzte zu färbende Zeile) wird mit einer Input-Box abgefragt.
- vor dem Einfärben wird eine beliebige Zelle in der letzten zu färbenden Zeile markiert. (bis zur Höhe dieser Zelle (also der entsprechenden Zeile) wird dann eingefärbt.

bye
malSchauen

Answer 4

Hi Erwin,

ein und der selbe Name lässt sich nicht so ohne Weiteres auf mehreren Tabellenblättern verwenden (zumindest nicht in den Excelversionen vor 2007) - das geht nur, wenn du in einem Tabellenblatt den Namen definierst und das Tabellenblatt anschließend kopierst. Der zuerst definierte Name ist dabei global und wird in jedem Tabellenblatt unter Einfügen -> Definieren -> Namen angezeigt. Der Name in dem kopierten Tabellenblatt ist lokal definiert und wird nur angezeigt, wenn du dich in dem betreffenden Tabellenblatt befindest, wobei der Bezug auf das Tabellenblatt eingetragen ist.

Namen kann man auch per VBA definieren, wobei sich lokale Namen auf diesem Weg erstellen lassen:

Worksheets("Tabelle4").Names.Add Name:="Hallo", _
RefersToLocal:=Worksheets("Tabelle4").Range("A1:C1")

Wenn du dich in deinem Code nun auf einen lokal definierten Namen beziehen willst, musst du den Tabellennamen mit angeben:

Worksheets("Tabelle4").Range("Hallo").Interior.ColorIndex = 4

Bis später,
Karin

Answer 5

Hallo malSchauen,

für mich wären alle drei Möglichkeiten praktikabel weil mit den verschiedenen Tabellen unterschiedliche Anwender arbeiten die mehr oder weniger Erfahrung mit Excel haben.

Für mögliche Teile der Prozedur wäre ich dir dankbar.


Gruß

EKG

Answer 6

Hi,

malSchauen, ob folgendes für Dich und Deine Anwender praktikabel ist.

Dim r As Range
Dim lngBZ As Long

lngBZ = Selection.Row
If lngBZ < 2 Then lngBZ = 2
If (MsgBox("Sollen die Wochenenden bis zur Zeile " & lngBZ & vbCrLf _
& "eingefärbt werden?" & vbCrLf & vbCrLf _
& "Ja = Einfärben bis Zeile " & lngBZ & vbCrLf _
& "Nein = Abbruch (Möglichkeit neue Zelle/Zeile zu wählen)" _
, vbYesNo + vbQuestion + vbDefaultButton2)) = 7 Then Exit Sub

For Each r In Range("a2:af2") ' erste Zeile der Tabelle
If IsDate(r.Value) Then
Select Case Weekday(r.Value)
Case 1
Range(Cells(2, r.Column), Cells(lngBZ, r.Column)).Interior.Color = vbBlue
Range(Cells(2, r.Column), Cells(lngBZ, r.Column)).Font.Color = vbWhite
Case 7
Range(Cells(2, r.Column), Cells(lngBZ, r.Column)).Interior.Color = vbBlue
Range(Cells(2, r.Column), Cells(lngBZ, r.Column)).Font.Color = vbWhite
Case Else
End Select
End If
Next r

bye
malSchauen

Answer 7

Hallo malSchauen

das waren deine Alternativen
- Anzahl der zu färbenden Zeilen (bzw. letzte zu färbende Zeile) wird mit einer Input-Box abgefragt.

Frage: Kann ich die Anzahl der zu färbenden Zeilen nicht nur abfragen sondern auch eingeben sprich mit einer Art Abfrage (aus Excel heraus) vorgeben?

d.h. von Zeile 2 Range(Cells(2, r.Column),
bis Zeile Vorwahl (ingBZ) Cells(lngBZ, r.Column)).Interior.Color


Frage: Wie lautet der Code für diese Möglichkeit

- vor dem Einfärben wird eine beliebige Zelle in der letzten zu färbenden Zeile markiert. (bis zur Höhe dieser Zelle (also der entsprechenden Zeile) wird dann eingefärbt.

Gruß
Erwin

Answer 8

Hi,

Eine Abfrage könnte man z.B. wie folgt gestalten:

Dim r As Range
Dim lngBZ As Variant

lngBZ = Application.InputBox("Bitte geben Sie die ZeilenNummer der" & vbCrLf _
& "letzten zu färbenden Zeile hier ein.", _
"Eingabe", , , , , , 1)

If lngBZ Then
If lngBZ < 2 Then lngBZ = 2
If lngBZ > Rows.Count Then lngBZ = Rows.Count
For Each r In Range("a2:af2") ' erste Zeile der Tabelle
If IsDate(r.Value) Then
Select Case Weekday(r.Value)
Case 1
Range(Cells(2, r.Column), Cells(lngBZ, r.Column)).Interior.Color = vbBlue
Range(Cells(2, r.Column), Cells(lngBZ, r.Column)).Font.Color = vbWhite
Case 7
Range(Cells(2, r.Column), Cells(lngBZ, r.Column)).Interior.Color = vbBlue
Range(Cells(2, r.Column), Cells(lngBZ, r.Column)).Font.Color = vbWhite
Case Else
End Select
End If
Next r
End If

bye
malSchauen

Answer 9

Hallo MalSchauen

nochmals großen Dank perfekte Lösung


Tschau und Grüße

Erwin

Ps.:

Auch an Beverly schönen Dank für die Hlfe