• Privacywetgeving
    Het is bij Helpmij.nl niet toegestaan om persoonsgegevens in een voorbeeld te plaatsen. Alle voorbeelden die persoonsgegevens bevatten zullen zonder opgaaf van reden verwijderd worden. In de vraag zal specifiek vermeld moeten worden dat het om fictieve namen gaat.

Tekst genereren

Status
Niet open voor verdere reacties.
S

Steve22

Gebruiker
Lid geworden
27 feb 2009
Berichten
18
Wat ik probeer is dat als in kolom C door middel van een keuzelijst 'Onderwijs' of 'Zorg' wordt gekozen er in kolom R 'N.v.t.' komt te staan. Ik ga alleen ergens de mist in met de code... misschien dat iemand me hierbij kan helpen? Het gaat om het laaste stukje...

Bedankt,
Steve

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)


     
     If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
        If Not Intersect(Target, Range("A1:A10000")) Is Nothing Then

        Select Case Target.Value
        Case vbNullString
          Target.Interior.ColorIndex = xlNone
        Target.Font.Bold = False
      
              
        Case 1
        Target.Interior.ColorIndex = 3
            Target.Font.Bold = True
            Target.Offset(0, 4).Value = "N.v.t."
            Target.Offset(0, 9).Value = "N.v.t."
            Target.Offset(0, 10).Value = "N.v.t."
            Target.Offset(0, 11).Value = "N.v.t."
            Target.Offset(0, 12).Value = "N.v.t."
            Target.Offset(0, 1).Interior.ColorIndex = Target.Interior.ColorIndex
        Case 2
            Target.Interior.ColorIndex = 46
            Target.Font.Bold = True
            Target.Offset(0, 4).Value = "N.v.t."
            Target.Offset(0, 9).Value = ""
            Target.Offset(0, 10).Value = ""
            Target.Offset(0, 11).Value = ""
            Target.Offset(0, 1).Interior.ColorIndex = Target.Interior.ColorIndex
        Case 3
            Target.Interior.ColorIndex = 6
            Target.Font.Bold = True
            Target.Offset(0, 4).Value = ""
            Target.Offset(0, 9).Value = ""
            Target.Offset(0, 10).Value = ""
            Target.Offset(0, 11).Value = ""
            Target.Offset(0, 1).Interior.ColorIndex = Target.Interior.ColorIndex
        Case 4
            Target.Interior.ColorIndex = 36
            Target.Font.Bold = True
            Target.Offset(0, 4).Value = ""
            Target.Offset(0, 9).Value = ""
            Target.Offset(0, 10).Value = ""
            Target.Offset(0, 11).Value = ""
            Target.Offset(0, 1).Interior.ColorIndex = Target.Interior.ColorIndex
        Case 5
            Target.Interior.ColorIndex = 35
            Target.Font.Bold = True
            Target.Offset(0, 4).Value = ""
            Target.Offset(0, 9).Value = ""
            Target.Offset(0, 10).Value = ""
            Target.Offset(0, 11).Value = ""
            Target.Offset(0, 1).Interior.ColorIndex = Target.Interior.ColorIndex
        Case 6
            Target.Interior.ColorIndex = 4
            Target.Font.Bold = True
            Target.Offset(0, 4).Value = ""
            Target.Offset(0, 9).Value = ""
            Target.Offset(0, 10).Value = ""
            Target.Offset(0, 11).Value = ""
            Target.Offset(0, 1).Interior.ColorIndex = Target.Interior.ColorIndex
        Case 7
            Target.Interior.ColorIndex = 50
            Target.Font.Bold = True
            Target.Offset(0, 4).Value = ""
            Target.Offset(0, 9).Value = ""
            Target.Offset(0, 10).Value = ""
            Target.Offset(0, 11).Value = ""
            Target.Offset(0, 1).Interior.ColorIndex = Target.Interior.ColorIndex
        Case 8
            Target.Interior.ColorIndex = 2
            Target.Font.Bold = True
            Target.Offset(0, 4).Value = ""
            Target.Offset(0, 9).Value = ""
            Target.Offset(0, 10).Value = ""
            Target.Offset(0, 11).Value = ""
            Target.Offset(0, 1).Interior.ColorIndex = Target.Interior.ColorIndex
        Case 9
            Target.Interior.ColorIndex = 1
            Target.Font.Bold = True
            Target.Offset(0, 4).Value = ""
            Target.Offset(0, 9).Value = ""
            Target.Offset(0, 10).Value = ""
            Target.Offset(0, 11).Value = ""
            Target.Offset(0, 1).Interior.ColorIndex = Target.Interior.ColorIndex
        Case "CAAML-proof"
            Target.Interior.ColorIndex = 35
            Target.Font.Bold = False
            Target.Offset(0, 4).Value = ""
            Target.Offset(0, 9).Value = ""
            Target.Offset(0, 10).Value = ""
            Target.Offset(0, 11).Value = ""
        Case "Statuten ontbreken"
            Target.Interior.ColorIndex = 46
            Target.Font.Bold = False
            Target.Offset(0, 4).Value = ""
            Target.Offset(0, 9).Value = ""
            Target.Offset(0, 10).Value = ""
            Target.Offset(0, 11).Value = ""
        Case "Niet CAAML-proof"
            Target.Interior.ColorIndex = 3
            Target.Font.Bold = False
            End Select
            
            Else
            If Not Intersect(Target, Range("F3:H10000")) Is Nothing Then
            
            Select Case Target.Value
            Case "CAAML-proof"
            Target.Interior.ColorIndex = 35
            Target.Offset(0, 1).Value = "N.v.t."
            Target.Offset(0, 2).Value = "N.v.t."
            Target.Interior.ColorIndex = 35
            Case "Statuten ontbreken"
            
            Target.Offset(0, 1).Value = "N.v.t."
            Target.Offset(0, -1).Value = "N.v.t."
             Target.Interior.ColorIndex = 45
            Case "Niet CAAML-proof"
            Target.Interior.ColorIndex = 3
            Target.Offset(0, -2).Value = "N.v.t."
            Target.Offset(0, -1).Value = "N.v.t."
            Target.Interior.ColorIndex = 3

            Else
            If Not Intersect(Target, Range("C3:C10000")) Is Nothing Then
             Case "Onderwijs"
            Target.Offset(0, 15).Value = "N.v.t."
            Case "Zorg"
            Target.Offset(0, 15).Value = "N.v.t."

        End Select
        End If
        End If
     
End Sub
 
Zo gaat het wel goed.
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)


     
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
    If Not Intersect(Target, Range("A1:A10000")) Is Nothing Then

        Select Case Target.Value
        Case vbNullString
          Target.Interior.ColorIndex = xlNone
        Target.Font.Bold = False
      
              
        Case 1
        Target.Interior.ColorIndex = 3
            Target.Font.Bold = True
            Target.Offset(0, 4).Value = "N.v.t."
            Target.Offset(0, 9).Value = "N.v.t."
            Target.Offset(0, 10).Value = "N.v.t."
            Target.Offset(0, 11).Value = "N.v.t."
            Target.Offset(0, 12).Value = "N.v.t."
            Target.Offset(0, 1).Interior.ColorIndex = Target.Interior.ColorIndex
        Case 2
            Target.Interior.ColorIndex = 46
            Target.Font.Bold = True
            Target.Offset(0, 4).Value = "N.v.t."
            Target.Offset(0, 9).Value = ""
            Target.Offset(0, 10).Value = ""
            Target.Offset(0, 11).Value = ""
            Target.Offset(0, 1).Interior.ColorIndex = Target.Interior.ColorIndex
        Case 3
            Target.Interior.ColorIndex = 6
            Target.Font.Bold = True
            Target.Offset(0, 4).Value = ""
            Target.Offset(0, 9).Value = ""
            Target.Offset(0, 10).Value = ""
            Target.Offset(0, 11).Value = ""
            Target.Offset(0, 1).Interior.ColorIndex = Target.Interior.ColorIndex
        Case 4
            Target.Interior.ColorIndex = 36
            Target.Font.Bold = True
            Target.Offset(0, 4).Value = ""
            Target.Offset(0, 9).Value = ""
            Target.Offset(0, 10).Value = ""
            Target.Offset(0, 11).Value = ""
            Target.Offset(0, 1).Interior.ColorIndex = Target.Interior.ColorIndex
        Case 5
            Target.Interior.ColorIndex = 35
            Target.Font.Bold = True
            Target.Offset(0, 4).Value = ""
            Target.Offset(0, 9).Value = ""
            Target.Offset(0, 10).Value = ""
            Target.Offset(0, 11).Value = ""
            Target.Offset(0, 1).Interior.ColorIndex = Target.Interior.ColorIndex
        Case 6
            Target.Interior.ColorIndex = 4
            Target.Font.Bold = True
            Target.Offset(0, 4).Value = ""
            Target.Offset(0, 9).Value = ""
            Target.Offset(0, 10).Value = ""
            Target.Offset(0, 11).Value = ""
            Target.Offset(0, 1).Interior.ColorIndex = Target.Interior.ColorIndex
        Case 7
            Target.Interior.ColorIndex = 50
            Target.Font.Bold = True
            Target.Offset(0, 4).Value = ""
            Target.Offset(0, 9).Value = ""
            Target.Offset(0, 10).Value = ""
            Target.Offset(0, 11).Value = ""
            Target.Offset(0, 1).Interior.ColorIndex = Target.Interior.ColorIndex
        Case 8
            Target.Interior.ColorIndex = 2
            Target.Font.Bold = True
            Target.Offset(0, 4).Value = ""
            Target.Offset(0, 9).Value = ""
            Target.Offset(0, 10).Value = ""
            Target.Offset(0, 11).Value = ""
            Target.Offset(0, 1).Interior.ColorIndex = Target.Interior.ColorIndex
        Case 9
            Target.Interior.ColorIndex = 1
            Target.Font.Bold = True
            Target.Offset(0, 4).Value = ""
            Target.Offset(0, 9).Value = ""
            Target.Offset(0, 10).Value = ""
            Target.Offset(0, 11).Value = ""
            Target.Offset(0, 1).Interior.ColorIndex = Target.Interior.ColorIndex
        Case "CAAML-proof"
            Target.Interior.ColorIndex = 35
            Target.Font.Bold = False
            Target.Offset(0, 4).Value = ""
            Target.Offset(0, 9).Value = ""
            Target.Offset(0, 10).Value = ""
            Target.Offset(0, 11).Value = ""
        Case "Statuten ontbreken"
            Target.Interior.ColorIndex = 46
            Target.Font.Bold = False
            Target.Offset(0, 4).Value = ""
            Target.Offset(0, 9).Value = ""
            Target.Offset(0, 10).Value = ""
            Target.Offset(0, 11).Value = ""
        Case "Niet CAAML-proof"
            Target.Interior.ColorIndex = 3
            Target.Font.Bold = False
        End Select
            
    Else
        If Not Intersect(Target, Range("F3:H10000")) Is Nothing Then
        
            Select Case Target.Value
            Case "CAAML-proof"
            Target.Interior.ColorIndex = 35
            Target.Offset(0, 1).Value = "N.v.t."
            Target.Offset(0, 2).Value = "N.v.t."
            Target.Interior.ColorIndex = 35
            Case "Statuten ontbreken"
            
            Target.Offset(0, 1).Value = "N.v.t."
            Target.Offset(0, -1).Value = "N.v.t."
             Target.Interior.ColorIndex = 45
            Case "Niet CAAML-proof"
            Target.Interior.ColorIndex = 3
            Target.Offset(0, -2).Value = "N.v.t."
            Target.Offset(0, -1).Value = "N.v.t."
            Target.Interior.ColorIndex = 3
        End Select

        Else
            If Not Intersect(Target, Range("C3:C10000")) Is Nothing Then
                Select Case Target.Value
                
                     Case "Onderwijs"
                    Target.Offset(0, 15).Value = "N.v.t."
                    Case "Zorg"
                    Target.Offset(0, 15).Value = "N.v.t."
                End Select
            End If
        End If
    End If
End Sub
Wellicht ten overvloede kan ik melden dat je code korter kan.

Met vriendelijke groet,


Roncancio
 
Bedankt voor de hulp Roncancio, het werkt perfect.

En ja meer mensen vertelden me dat het korter kan maar ik ga me er niet aan wagen :p heb het nu eindelijk werkende dus ik vind het wel prima.

Bedankt voor je hulp!
 
Status
Niet open voor verdere reacties.
                    Steun ons                    

Nieuwste berichten

Terug
Bovenaan Onderaan