VBA Colorarray
16.09.2014 17:08:32
Peter
Option Explicit
Sub Geldhaushalt()
Dim rngCell As Excel.Range
'erste Zelle referenzieren
Set rngCell = Worksheets("Tabelle3").Range("I4")
'solange verarbeiten bis Zelle leer ist
Do Until IsEmpty(rngCell.Value)
Select Case Trim$(rngCell.Text)
'Gruppe 1
Case "E1", "E2", "E3", "EE1", "EE2", "EE3", "EE4"
rngCell.Interior.Color = RGB(0, 128, 0)
'Gruppe 2
Case "T1", "T2", "T3", "T4", "T5", "TT1", "TT2", "TT3"
rngCell.Interior.Color = RGB(0, 204, 255)
'Gruppe 3
Case "Z1", "Z2", "Z3"
rngCell.Interior.Color = RGB(153, 51, 0)
'Gruppe 4
Case "U1", "U2", "U3"
rngCell.Interior.Color = RGB(153, 51, 102)
'Gruppe 5
Case "K", "TEC", "NEC"
rngCell.Interior.Color = RGB(51, 51, 51)
'Gruppe 6
Case "STR", "ENT", "KUR", "SOF", "SER", "ORG", "RE"
rngCell.Interior.Color = RGB(255, 0, 0)
'.. und ansonsten
Case Else
rngCell.Interior.ColorIndex = xlColorIndexNone
End Select
'nächste Zelle referenzieren (= eine tiefer)
Set rngCell = rngCell.Offset(RowOffset:=1)
Loop
End Sub
Der Link zur Tabelle: https://www.herber.de/bbs/user/92665.xlsm