wouter19883
Nieuwe gebruiker
- Lid geworden
- 21 nov 2020
- Berichten
- 4
Wie kan mijn VBA eenvoudiger maken?
ALS Cel R8 de waarde 21 bevat(uitkomst van een formulle) Maar geld dan ook voor R9, R10 ETC....
dan de cellen"a8,B8,F8,G8,h8,k8,i8" in dezelfde rij leegmaken (niet verwijderen)
Wie o wie heeft een passende VBA code
Alvast enorm bedank!!
Ps onderstaand is nu de code maar ik denk dat het eenvoudiger kan.
ALS Cel R8 de waarde 21 bevat(uitkomst van een formulle) Maar geld dan ook voor R9, R10 ETC....
dan de cellen"a8,B8,F8,G8,h8,k8,i8" in dezelfde rij leegmaken (niet verwijderen)
Wie o wie heeft een passende VBA code
Alvast enorm bedank!!
Ps onderstaand is nu de code maar ik denk dat het eenvoudiger kan.
Code:
sheets("Bestellijst").Unprotect "Rietveld19883"
If Range("R8").Value = 21 Then
Sheets("Bestellijst").Unprotect "Rietveld19883"
Range("a8,B8,F8,G8,h8,k8,i8").Value = ""
Range("K8").Activate
Sheets("Bestellijst").Unprotect "Rietveld19883"
Selection.Font.Underline = xlUnderlineStyleNone
With Selection.Font
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
End With
Range("B8").Activate
Range("h8").Activate
Sheets("Bestellijst").Protect "Rietveld19883"
End If
If Range("R9").Value = 21 Then
Range("a9,B9,F9,G9,h9,K9,I9").Value = ""
Range("K9").Select
Sheets("Bestellijst").Unprotect "Rietveld19883"
Selection.Font.Underline = xlUnderlineStyleNone
With Selection.Font
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
End With
Range("B9").Activate
Range("h9").Activate
Sheets("Bestellijst").Protect "Rietveld19883"
End If
If Range("R10").Value = 21 Then
Range("a10,B10,f10,G10,h10,K10,i10").Value = ""
Range("K10").Select
Sheets("Bestellijst").Unprotect "Rietveld19883"
Selection.Font.Underline = xlUnderlineStyleNone
With Selection.Font
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
End With
Range("B10").Activate
Range("h10").Activate
Sheets("Bestellijst").Protect "Rietveld19883"
End If
If Range("R11").Value = 21 Then
Range("a11,B11,f11,G11,h11,K11,i11").Value = ""
Range("K11").Select
Sheets("Bestellijst").Unprotect "Rietveld19883"
Selection.Font.Underline = xlUnderlineStyleNone
With Selection.Font
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
End With
Range("B11").Activate
Range("h11").Activate
Sheets("Bestellijst").Protect "Rietveld19883"
End If
If Range("R12").Value = 21 Then
Range("a12,B12,f12,G12,h12,K12,i12").Value = ""
Range("K12").Select
Sheets("Bestellijst").Unprotect "Rietveld19883"
Selection.Font.Underline = xlUnderlineStyleNone
With Selection.Font
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
End With
Range("B12").Activate
Range("H12").Activate
Sheets("Bestellijst").Protect "Rietveld19883"
End If
If Range("R13").Value = 21 Then
Range("a13,B13,f13,G13,h13,K13,i13").Value = ""
Range("K13").Select
Sheets("Bestellijst").Unprotect "Rietveld19883"
Selection.Font.Underline = xlUnderlineStyleNone
With Selection.Font
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
End With
Range("B13").Activate
Range("h13").Activate
Sheets("Bestellijst").Protect "Rietveld19883"
End If
If Range("R14").Value = 21 Then
Range("a14,B14,f14,G14,h14,K14,i14").Value = ""
Range("K14").Select
Sheets("Bestellijst").Unprotect "Rietveld19883"
Selection.Font.Underline = xlUnderlineStyleNone
With Selection.Font
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
End With
Range("B14").Activate
Range("h14").Activate
Sheets("Bestellijst").Protect "Rietveld19883"
End If
If Range("R15").Value = 21 Then
Range("a15,B15,f15,G15,h15,K15,i15").Value = ""
Range("K15").Select
Sheets("Bestellijst").Unprotect "Rietveld19883"
Selection.Font.Underline = xlUnderlineStyleNone
With Selection.Font
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
End With
Range("B15").Activate
Range("h15").Activate
Sheets("Bestellijst").Protect "Rietveld19883"
End If
If Range("R16").Value = 21 Then
Range("a16,B16,f16,G16,h16,K16,i16").Value = ""
Range("K16").Select
Sheets("Bestellijst").Unprotect "Rietveld19883"
Selection.Font.Underline = xlUnderlineStyleNone
With Selection.Font
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
End With
Range("B16").Activate
Range("h16").Activate
Sheets("Bestellijst").Protect "Rietveld19883"
End If
If Range("R17").Value = 21 Then
Range("a17,B17,f17,G17,h17,K17,i17").Value = ""
Range("K17").Select
Sheets("Bestellijst").Unprotect "Rietveld19883"
Selection.Font.Underline = xlUnderlineStyleNone
With Selection.Font
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
End With
Range("B17").Activate
Range("h17").Activate
Sheets("Bestellijst").Protect "Rietveld19883"
End If
If Range("R18").Value = 21 Then
Range("a18,B18,f18,G18,h18,K18,i18").Value = ""
Range("K18").Select
Sheets("Bestellijst").Unprotect "Rietveld19883"
Selection.Font.Underline = xlUnderlineStyleNone
With Selection.Font
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
End With
Range("B18").Activate
Range("h18").Activate
Sheets("Bestellijst").Protect "Rietveld19883"
End If
If Range("R19").Value = 21 Then
Range("a19,B19,f19,G19,h19,K19,i19").Value = ""
Range("K19").Select
Sheets("Bestellijst").Unprotect "Rietveld19883"
Selection.Font.Underline = xlUnderlineStyleNone
With Selection.Font
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
End With
Range("B19").Activate
Range("h19").Activate
Sheets("Bestellijst").Protect "Rietveld19883"
End If
If Range("R20").Value = 21 Then
Range("a20,B20,f20,G20,h20,K20,i20").Value = ""
Range("K20").Select
Sheets("Bestellijst").Unprotect "Rietveld19883"
Selection.Font.Underline = xlUnderlineStyleNone
With Selection.Font
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
End With
Range("B20").Activate
Range("h20").Activate
Sheets("Bestellijst").Protect "Rietveld19883"
End If
If Range("R21").Value = 21 Then
Range("a21,B21,f21,G21,h21,K21,i21").Value = ""
Range("K21").Select
Sheets("Bestellijst").Unprotect "Rietveld19883"
Selection.Font.Underline = xlUnderlineStyleNone
With Selection.Font
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
End With
Range("B21").Activate
Range("h21").Activate
Sheets("Bestellijst").Protect "Rietveld19883"
End If
If Range("R22").Value = 21 Then
Range("a22,B22,f22,G22,h22,K22,i22").Value = ""
Range("K22").Select
Sheets("Bestellijst").Unprotect "Rietveld19883"
Selection.Font.Underline = xlUnderlineStyleNone
With Selection.Font
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
End With
Range("B22").Activate
Range("h22").Activate
Sheets("Bestellijst").Protect "Rietveld19883"
End If
If Range("R23").Value = 21 Then
Range("a23,B23,f23,G23,h23,K23,i23").Value = ""
Range("K23").Select
Sheets("Bestellijst").Unprotect "Rietveld19883"
Selection.Font.Underline = xlUnderlineStyleNone
With Selection.Font
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
End With
Range("B23").Activate
Range("h23").Activate
Sheets("Bestellijst").Protect "Rietveld19883"
End If
If Range("R24").Value = 21 Then
Range("a24,B24,f24,G24,h24,K24,i24").Value = ""
Range("K24").Select
Sheets("Bestellijst").Unprotect "Rietveld19883"
Selection.Font.Underline = xlUnderlineStyleNone
With Selection.Font
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
End With
Range("B24").Activate
Range("h24").Activate
Sheets("Bestellijst").Protect "Rietveld19883"
End If