johnny1980
Gebruiker
- Lid geworden
- 28 apr 2013
- Berichten
- 75
Beste helpers,
Graag jullie raad/advies bij deze:
Na het ingeven van deze code werkt m'n excel-bestand trager, moet ik hier iets aan toevoegen (dim as,...…. )
Is de verwijzing van de range correct?
Het werkt wel maar soms moet excel precies te lang zoeken of vergelijken,.....
Thx,
Johnny
Graag jullie raad/advies bij deze:
Code:
'Kleur Via
For Each v In Range("K3:K" & Range("K300").End(xlUp).Row)
If v = "FBN" Then
Range("K" & v.Row).Interior.ColorIndex = 43
ElseIf v = "FVV" Then
Range("K" & v.Row).Interior.ColorIndex = 45
ElseIf v = "NVT" Then
Range("K" & v.Row).Interior.ColorIndex = xlNone
End If
Next
' Rand
For Each r In Range("A3:A" & Range("A300").End(xlUp).Row)
If r = Value >= 0 Then
Range("A" & r.Row, "S" & r.Row).Borders.Weight = xlThin
'Range("M" & r.Row, "O" & r.Row).Borders.Color = vbRed
Range("M" & r.Row, "O" & r.Row).Borders.Weight = 3
End If
Next
'Spoor
For Each s In Range("L3:L" & Range("L300").End(xlUp).Row)
If s = Value >= 0 Then
Range("L" & s.Row).Interior.ColorIndex = 36
ElseIf s = "" Then
Range("L" & s.Row).Interior.ColorIndex = xlNone
End If
Next
'Opmaak vertrek
For Each c In Range("F3:F" & Range("F300").End(xlUp).Row)
If c = "SCHAARB.-VORM. BUNDEL C" Then
Range("F" & c.Row, "G" & c.Row).Font.ColorIndex = 5
Range("F" & c.Row, "I" & c.Row).Font.Bold = xlMedium
Range("A" & c.Row, "D" & c.Row).Font.ColorIndex = 5
Range("A" & c.Row, "D" & c.Row).Font.Bold = xlMedium
Range("H" & c.Row, "I" & c.Row).Font.ColorIndex = 5
Range("N" & c.Row, "N" & c.Row).Font.ColorIndex = 5
Range("R" & c.Row, "R" & c.Row).Font.ColorIndex = 5
Range("R" & c.Row, "R" & c.Row).Font.Bold = xlMedium
ElseIf c = "SCHAARB.-VORM. BUNDEL R" Then
Range("F" & c.Row, "G" & c.Row).Font.ColorIndex = 3
Range("F" & c.Row, "I" & c.Row).Font.Bold = xlMedium
Range("A" & c.Row, "D" & c.Row).Font.ColorIndex = 3
Range("A" & c.Row, "D" & c.Row).Font.Bold = xlMedium
Range("H" & c.Row, "I" & c.Row).Font.ColorIndex = 3
Range("N" & c.Row, "N" & c.Row).Font.ColorIndex = 3
Range("R" & c.Row, "R" & c.Row).Font.ColorIndex = 3
Range("R" & c.Row, "R" & c.Row).Font.Bold = xlMedium
ElseIf c = "SCHAARB.-VORM. BUNDEL P" Then
Range("F" & c.Row, "G" & c.Row).Font.ColorIndex = 10
Range("F" & c.Row, "I" & c.Row).Font.Bold = xlMedium
Range("A" & c.Row, "D" & c.Row).Font.ColorIndex = 10
Range("A" & c.Row, "D" & c.Row).Font.Bold = xlMedium
Range("H" & c.Row, "I" & c.Row).Font.ColorIndex = 10
Range("N" & c.Row, "N" & c.Row).Font.ColorIndex = 10
Range("R" & c.Row, "R" & c.Row).Font.ColorIndex = 10
Range("R" & c.Row, "R" & c.Row).Font.Bold = xlMedium
ElseIf c = "SCHAARB.-VORM. BUNDEL L" Then
Range("F" & c.Row, "G" & c.Row).Font.ColorIndex = 3
Range("F" & c.Row, "I" & c.Row).Font.Bold = xlMedium
Range("A" & c.Row, "D" & c.Row).Font.ColorIndex = 3
Range("A" & c.Row, "D" & c.Row).Font.Bold = xlMedium
Range("H" & c.Row, "I" & c.Row).Font.ColorIndex = 3
Range("N" & c.Row, "N" & c.Row).Font.ColorIndex = 3
Range("R" & c.Row, "R" & c.Row).Font.ColorIndex = 3
Range("R" & c.Row, "R" & c.Row).Font.Bold = xlMedium
End If
Next
'Opmaak aankomst
For Each o In Range("G3:G" & Range("G300").End(xlUp).Row)
If o = "SCHAARB.-VORM. BUNDEL C" Then
Range("G" & o.Row).Font.ColorIndex = 1
ElseIf o = "SCHAARB.-VORM. BUNDEL R" Then
Range("G" & o.Row).Font.ColorIndex = 3
ElseIf o = "SCHAARB.-VORM. BUNDEL P" Then
Range("G" & o.Row).Font.ColorIndex = 10
ElseIf o = "SCHAARB.-VORM. BUNDEL L" Then
Range("G" & o.Row).Font.ColorIndex = 3
End If
Next
For Each o In Range("R3:R" & Range("R300").End(xlUp).Row)
If o = "BOOK IN" & Chr(10) & "??" Then
Range("A" & o.Row, "R" & o.Row).Interior.ColorIndex = 20
End If
Next
Na het ingeven van deze code werkt m'n excel-bestand trager, moet ik hier iets aan toevoegen (dim as,...…. )
Is de verwijzing van de range correct?
Het werkt wel maar soms moet excel precies te lang zoeken of vergelijken,.....
Thx,
Johnny
Bijlagen
Laatst bewerkt: