1 VBA voor alle tabbladen

[Helpmij7]

Hallo,

Ik heb een vba gemaakt.
Deze heb ik op blad 1 (handleiding) geplaatst.
Nu wil ik dat deze vba op alle tabbladen toegepast wordt.
Hoe krijg ik dit voor elkaar?

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
   
   With Target
   
    Select Case .Value
    
     Case Is = "BcD"
          .Font.ColorIndex = 6
          .Interior.ColorIndex = 10
          
    Case Is = "CIT"
          .Font.ColorIndex = 1
          .Interior.ColorIndex = 8
                
     Case Is = "CU"
          .Interior.ColorIndex = 3
          
        Case Is = "CL"
          .Font.ColorIndex = 6
          .Interior.ColorIndex = 5
          
       Case Is = "CV"
          .Font.ColorIndex = 6
          .Interior.ColorIndex = 10
          
      Case Is = "E"
          .Interior.ColorIndex = 36
         
          
      Case Is = "P"
          .Interior.ColorIndex = 6
          
           
      Case Is = "TIN"
          .Font.ColorIndex = 1
          .Interior.ColorIndex = 8
          
      Case Is = "L"
          .Font.ColorIndex = 6
          .Interior.ColorIndex = 5
               
      
      Case Is = "IL"
          .Font.ColorIndex = 6
          .Interior.ColorIndex = 5
          
     Case Is = "InV"
          .Font.ColorIndex = 6
          .Interior.ColorIndex = 10
          
          Case Is = "InL"
          .Font.ColorIndex = 6
          .Interior.ColorIndex = 5
          
      Case Is = "V"
          .Interior.ColorIndex = 4
          
       Case Is = "DV"
          .Interior.ColorIndex = 4
          
      Case Is = "VC"
          .Interior.ColorIndex = 4
          
      Case Is = "S"
          .Interior.ColorIndex = 7
          
          Case Is = "TL"
          .Font.ColorIndex = 6
          .Interior.ColorIndex = 5
          
          
      Case Is = "O"
          .Font.ColorIndex = 6
          .Interior.ColorIndex = 10
          
      
          
      Case Is = "To"
          .Font.ColorIndex = 6
          .Interior.ColorIndex = 10
          
      Case Is = "Z"
          .Interior.ColorIndex = 44
          
      
          
      Case Else
          .Interior.ColorIndex = xlNone
          
    End Select

   End With
   
   If Target.Value = "C" Then
     Target.Value = "Ci"
   End If
   
   If Target.Value = "I" Then
     Target.Value = "In"
   End If
   
   If Target.Value = "B" Then
     Target.Value = "BcD"
   End If
   
  If Target.Value = "TT" Then
      Target.Value = "T2T"
   End If
   
  
   
  End Sub

Heb tabbladen met de namen:

worksheetnaam: planning_2011.xls

met de onderliggende tabbladen:

handleding / Jan / Feb / Mrt / Apr / etc..



Hoop dat dit voldoende info is voor dit probleem.

 

[Roncancio]

Zet de code in ThisWorkbook bij Worksheet_SheetChange

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

With Target

Select Case .Value

Case Is = "BcD"
.Font.ColorIndex = 6
.Interior.ColorIndex = 10

Case Is = "CIT"
.Font.ColorIndex = 1
.Interior.ColorIndex = 8

Case Is = "CU"
.Interior.ColorIndex = 3

Case Is = "CL"
.Font.ColorIndex = 6
.Interior.ColorIndex = 5

Case Is = "CV"
.Font.ColorIndex = 6
.Interior.ColorIndex = 10

Case Is = "E"
.Interior.ColorIndex = 36


Case Is = "P"
.Interior.ColorIndex = 6


Case Is = "TIN"
.Font.ColorIndex = 1
.Interior.ColorIndex = 8

Case Is = "L"
.Font.ColorIndex = 6
.Interior.ColorIndex = 5


Case Is = "IL"
.Font.ColorIndex = 6
.Interior.ColorIndex = 5

Case Is = "InV"
.Font.ColorIndex = 6
.Interior.ColorIndex = 10

Case Is = "InL"
.Font.ColorIndex = 6
.Interior.ColorIndex = 5

Case Is = "V"
.Interior.ColorIndex = 4

Case Is = "DV"
.Interior.ColorIndex = 4

Case Is = "VC"
.Interior.ColorIndex = 4

Case Is = "S"
.Interior.ColorIndex = 7

Case Is = "TL"
.Font.ColorIndex = 6
.Interior.ColorIndex = 5


Case Is = "O"
.Font.ColorIndex = 6
.Interior.ColorIndex = 10



Case Is = "To"
.Font.ColorIndex = 6
.Interior.ColorIndex = 10

Case Is = "Z"
.Interior.ColorIndex = 44



Case Else
.Interior.ColorIndex = xlNone

End Select

End With

If Target.Value = "C" Then
Target.Value = "Ci"
End If

If Target.Value = "I" Then
Target.Value = "In"
End If

If Target.Value = "B" Then
Target.Value = "BcD"
End If

If Target.Value = "TT" Then
Target.Value = "T2T"
End If



End Sub

Met vriendelijke groet,


Roncancio

 

[Helpmij7]

Vba

Hallo Roncancio

Met een beetje geklingel ging het niet, totdat ik het licht zag en de xbook

Toen was het gelukt

bedankt

Antoon