AW: Formatierung durch Inhalt einer anderen zelle
09.01.2013 15:21:42
Klaus
Hi Hugo,
erweitere dein worksheet_change Makro entsprechend:
Option Explicit
Dim Bereich1 As Range
Private Sub Worksheet_Change(ByVal Target As Range)
Dim actCell As Range
Dim Bereich2 As Range
Set Bereich2 = Range("H4:Ab71")
Application.ScreenUpdating = False
For Each actCell In Bereich1
If Not Intersect(actCell, Bereich2) Is Nothing Then
Select Case actCell
Case "": actCell.Interior.ColorIndex = 2
Case "1": actCell.Interior.ColorIndex = 3
Case "2": actCell.Interior.ColorIndex = 4
Case "3": actCell.Interior.ColorIndex = 5
Case "4": actCell.Interior.ColorIndex = 6
Case "5": actCell.Interior.ColorIndex = 7
Case "6": actCell.Interior.ColorIndex = 8
Case "7": actCell.Interior.ColorIndex = 9
Case "8": actCell.Interior.ColorIndex = 10
Case "9": actCell.Interior.ColorIndex = 11
Case "10": actCell.Interior.ColorIndex = 12
Case "11": actCell.Interior.ColorIndex = 13
Case "12": actCell.Interior.ColorIndex = 14
Case "13": actCell.Interior.ColorIndex = 15
Case "14": actCell.Interior.ColorIndex = 16
Case "15": actCell.Interior.ColorIndex = 17
End Select
Select Case actCell
Case "": Range(Cells(actCell.Row, 4), Cells(actCell.Row, 6)).Interior.ColorIndex = _
2
Case "1": Range(Cells(actCell.Row, 4), Cells(actCell.Row, 6)).Interior.ColorIndex = _
3
Case "2": Range(Cells(actCell.Row, 4), Cells(actCell.Row, 6)).Interior.ColorIndex = _
4
Case "3": Range(Cells(actCell.Row, 4), Cells(actCell.Row, 6)).Interior.ColorIndex = _
5
Case "4": Range(Cells(actCell.Row, 4), Cells(actCell.Row, 6)).Interior.ColorIndex = _
6
Case "5": Range(Cells(actCell.Row, 4), Cells(actCell.Row, 6)).Interior.ColorIndex = _
7
Case "6": Range(Cells(actCell.Row, 4), Cells(actCell.Row, 6)).Interior.ColorIndex = _
8
Case "7": Range(Cells(actCell.Row, 4), Cells(actCell.Row, 6)).Interior.ColorIndex = _
9
Case "8": Range(Cells(actCell.Row, 4), Cells(actCell.Row, 6)).Interior.ColorIndex = _
10
Case "9": Range(Cells(actCell.Row, 4), Cells(actCell.Row, 6)).Interior.ColorIndex = _
11
Case "10": Range(Cells(actCell.Row, 4), Cells(actCell.Row, 6)).Interior.ColorIndex = _
12
Case "11": Range(Cells(actCell.Row, 4), Cells(actCell.Row, 6)).Interior.ColorIndex = _
13
Case "12": Range(Cells(actCell.Row, 4), Cells(actCell.Row, 6)).Interior.ColorIndex = _
14
Case "13": Range(Cells(actCell.Row, 4), Cells(actCell.Row, 6)).Interior.ColorIndex = _
15
Case "14": Range(Cells(actCell.Row, 4), Cells(actCell.Row, 6)).Interior.ColorIndex = _
16
Case "15": Range(Cells(actCell.Row, 4), Cells(actCell.Row, 6)).Interior.ColorIndex = _
17
End Select
End If
Next
Application.ScreenUpdating = True
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set Bereich1 = Selection
End Sub
LG zurück aus Estland,
Klaus M.vdT.