VBA in Excel

Hallo Leute,

ich bin gerade bei eine kleine Sache in VBA für Excel zu prgrammieren. Allerdings komme ich gerade nicht weiter und da ich ein absoluter VBA Anfänger bin, wollte ich nachfragen ob ihr mir evtl helfen könnt.

Ich will in Excel einen Wert vergleichen und wenn dieser größer ist als der Vergleichswert dann soll ein Bereich orange oder gold hinterlegt werden. Dazu hab ich jetzt folgenden code geschrieben:

Code:

Option Explicit

Public Sub vergleich()

Dim bereich3 As Range
Dim bereich4 As Range
Dim bereich5 As Range
Dim bereich6 As Range
Dim bereich7 As Range
Dim bereich8 As Range
Dim bereich9 As Range
Dim bereich10 As Range
Dim bereich11 As Range
Dim bereich12 As Range

Dim ausw3 As Range
Dim ausw4 As Range
Dim ausw5 As Range
Dim ausw6 As Range
Dim ausw7 As Range
Dim ausw8 As Range
Dim ausw9 As Range
Dim ausw10 As Range
Dim ausw11 As Range
Dim ausw12 As Range

Set bereich3 = Worksheets("Kirchhoff").Range("E3")
Set bereich4 = Worksheets("Kirchhoff").Range("E4")
Set bereich5 = Worksheets("Kirchhoff").Range("E5")
Set bereich6 = Worksheets("Kirchhoff").Range("E6")
Set bereich7 = Worksheets("Kirchhoff").Range("E7")
Set bereich8 = Worksheets("Kirchhoff").Range("E8")
Set bereich9 = Worksheets("Kirchhoff").Range("E9")
Set bereich10 = Worksheets("Kirchhoff").Range("E10")
Set bereich11 = Worksheets("Kirchhoff").Range("E11")
Set bereich12 = Worksheets("Kirchhoff").Range("D12")

Set ausw3 = Worksheets("Kirchhoff").Range("A3:E3")
Set ausw4 = Worksheets("Kirchhoff").Range("A4:E4")
Set ausw5 = Worksheets("Kirchhoff").Range("A5:E5")
Set ausw6 = Worksheets("Kirchhoff").Range("A6:E6")
Set ausw7 = Worksheets("Kirchhoff").Range("A7:E7")
Set ausw8 = Worksheets("Kirchhoff").Range("A8:E8")
Set ausw9 = Worksheets("Kirchhoff").Range("A9:E9")
Set ausw10 = Worksheets("Kirchhoff").Range("A10:E10")
Set ausw11 = Worksheets("Kirchhoff").Range("A11:E11")
Set ausw12 = Worksheets("Kirchhoff").Range("A12:E12")

If bereich3.Value > 100 Then ausw3 BackColor = vbRed
End If


End Sub

Könnt ihr mir sagen warum das nicht funktioniert und ob man den code evtl. etwas verkleinern kann?

 

Hallo Wesker!

Die letzte If-Then-Abfrage ist ... na ja.

Bei mir funktioniert das:

Code:

Option Explicit

Public Sub vergleich()
Dim Aus3, Aus4, Aus5 As Range
Dim Ber3, Ber4, Ber5 As Range

Set Ber3 = Worksheets("Tabelle1").Range("E3")
Set Ber4 = Worksheets("Tabelle1").Range("E4")
Set Ber5 = Worksheets("Tabelle1").Range("E5")

Set Aus3 = Worksheets("Tabelle1").Range("A3:E3")
Set Aus4 = Worksheets("Tabelle1").Range("A4:E4")
Set Aus5 = Worksheets("Tabelle1").Range("A5:E5")

If Ber3.Value > 100 Then
   Aus3.Select
   With Selection.Interior
    .ColorIndex = 3
   .Pattern = xlSolid
  End With
 Else
    Aus3.Select
    With Selection.Interior
    .ColorIndex = 2
    .Pattern = xlSolid
    End With
End If

End Sub

Die Bereiche und Auswahlen kann man in einer Zeile deklarieren.
Ich hab mal die Variablennamen gekürzt und die Tabelle auf den Namen "Tabelle1" belassen.

Das "Rücksetzen" des Hintergrundes funktioniert im Else-Zweig.
Alle Zellenrahmen werden nicht verändert.

Gruß chipchap

P.S.: Hieran habe ich mich orientiert:
http://www.wer-weiss-was.de/theme156/article3653639.html .

 

@chipchap
Im Gegensatz zu VB verlangt VBA nicht, dass Variablen As Irgendwas deklariert werden, wenn man es aber macht, dann muss in der Zeile jede Variable einzeln so deklariert werden, sonst werden sie automatisch zu Variant >
Dim Aus3, Aus4, Aus5 As Range >>> 3 + 4 sind Variant, nur 5 ist Range
Dim Aus3 As Range, Aus4 As Range, Aus5 As Range >>> alle drei sind Range


@Wesker
So wird automatisch bei einer Veränderung in Spalte E die entsprechende Zeile ge/entfärbt, wie in chipchaps Link

Code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rwTop, rwLow, colVal, isect

rwTop = [COLOR="Red"]3  [/COLOR]     'oberste zu überwachende Zeilennummer
rwLow = [COLOR="red"]12[/COLOR]      'unterste Zeilennummer
colVal = [COLOR="red"]5[/COLOR]      'zu überwachende Spalte > E = 5. Spalte

Set isect = Application.Intersect(Range(Cells(rwTop, colVal), Cells(rwLow, colVal)), Target)
If isect Then
    If Target.Value > 100 Then
        Range(Cells(Target.Row, 1), Cells(Target.Row, colVal)).Interior.ColorIndex = 27
    Else
        Range(Cells(Target.Row, 1), Cells(Target.Row, colVal)).Interior.ColorIndex = 0
    End If
End If

End Sub

Die Zahl für ColorIndex >>> In Deinem Excel in der Farbpalette die Musterquadrate abzählen.
In meinem Excel ist das 27. Quadrat = gelb.

Die roten Zahlen im Code ggf Deinem Blatt anpassen.
Das Makro muss in das Verzeichnis des Blattes, das die zu prüfenden Zahlen enthält.

Gruss Urs

 

Ja, hast Recht.
VBA ist eben NICHT VB.
Danke für die Korrektur.

Gruß chipchap

 

erstaml danke euch beiden für die Hilfe

@chipchap:
ich hab dein code zwar angepasst aber er läuft bei mir nicht. Es passiert nichts wenn ich in die Zeile E3 z. B. 120 eingebe

@Urs2
Dein Code funktioniert zwar grundsätzlich aber nach der 1. Eingabe taucht ein Laufzeitfehler 91 mit der Meldung auf:

Objektvariable oder With Blockvariable nicht festgelegt.

 

Bei mir funktioniert der Code von Urs2 tadellos :nixwissen .
Welches Excel benutzt Du?
Du mußt die entsprechenden Zellen als "Zahl" formatieren.

Bei Eingabe dieser Zeile:

Code:

        Range(Cells(Target.Row, 1), Cells(Target.Row, colVal)).Interior.Color = RGB(248, 242, 108)

in der If-Then-Else-Abfrage kannst Du die RGB-Werte für den Hintergrund aus einem Grafikprogram direkt ablesen und eingeben.

 

Ja, es gibt verschiedene Möglichkeiten die Farben zu definieren, jeder darf benutzen, was ihm am besten passt...

@Wesker
- erscheint im VB-Editor beim Fehler eine bestimmte Code-Zeile gelb hinterlegt, oder startet der Code gar nicht ?
- Excel-Version, wie schon angefragt ?
- im VB-Editor >Menü Extras >Verweise >>> Welche Verweise sind dort angehakt ?

Gruss Urs

 

also, ich hab jetzt folgendes gemacht:
- VBA-Editor aufgemacht und das alte Modul gelöscht
- neues Modul eingefügt, und deine Code eingefügt
- gespeichert und Editor und Excel geschlossen
- Datei neu aufgemacht und es passiert nichts

Dann hab ich nochmal den Editor aufgemacht und auf Ausführen -> Sub geklickt und dann musste ich feststellen dass diese Sub im Makrofenster gar nicht angezeigt wird.

Die Spalte E ist als Währung (€) formatiert
Excel Version ist 2003

 

ich nehme alles zurück, hab den code an falscher stelle eingefügt

nochmal vielen Dank für die Hilfe

 

Info >

Private Sub > werden in Excel>Extra>Makro nie angezeigt.
Sie können nur von einem Makro im gleichen Verzeichnis gestartet werden.
Das kann man verwenden... damit nicht jeder "Experte" dieses Makro starten kann...

Unser Makro ist noch spezieller, es ist ein Automakro.
Es muss nur ein vorbestimmtes Blatt überwachen, und muss deshalb in dessen Verzeichnis stehen.

Gruss Urs