Microsoft Excel

Excel und VBA: Formeln, Programmierung, Lösungen

Die Excel/VBA-Beispiele

Thema

Zellen in Abhängigkeit der aktuellen Uhrzeit markieren

Gruppe

Calculate

Problem

A1 soll von 22:01-06:00, B1 von 06:01 bis 14:00 und C1 von 14:01 bis 22:00 Uhr eine Kreuzmarkierung erhalten, Aktualisierung bei jeder Zelleingabe in Spalte A.

Lösung
Geben Sie den nachfolgenden Code in das Klassenmodul der UsereForm ein.

ClassModule: Tabelle1

Private Sub Worksheet_Calculate()
   Select Case Hour(Time)
      Case 22 To 24, 0 To 6
         With Range("A1")
            .Borders(xlDiagonalDown).Weight = xlThin
            .Borders(xlDiagonalUp).Weight = xlThin
         End With
         With Range("B1")
            .Borders(xlDiagonalDown).LineStyle = xlNone
            .Borders(xlDiagonalUp).LineStyle = xlNone
         End With
         With Range("C1")
            .Borders(xlDiagonalDown).LineStyle = xlNone
            .Borders(xlDiagonalUp).LineStyle = xlNone
         End With
      Case 6 To 14
         With Range("B1")
            .Borders(xlDiagonalDown).Weight = xlThin
            .Borders(xlDiagonalUp).Weight = xlThin
         End With
         With Range("A1")
            .Borders(xlDiagonalDown).LineStyle = xlNone
            .Borders(xlDiagonalUp).LineStyle = xlNone
         End With
         With Range("C1")
            .Borders(xlDiagonalDown).LineStyle = xlNone
            .Borders(xlDiagonalUp).LineStyle = xlNone
         End With
      Case 14 To 22
         With Range("C1")
            .Borders(xlDiagonalDown).Weight = xlThin
            .Borders(xlDiagonalUp).Weight = xlThin
         End With
         With Range("A1")
            .Borders(xlDiagonalDown).LineStyle = xlNone
            .Borders(xlDiagonalUp).LineStyle = xlNone
         End With
         With Range("B1")
            .Borders(xlDiagonalDown).LineStyle = xlNone
            .Borders(xlDiagonalUp).LineStyle = xlNone
         End With
   End Select
End Sub