Help!

PC-Problemen?
De vrijwilligers van Oplossing.be zoeken gratis met u mee!

Hulp bij posten

Recente topics

Auteur Topic: VBA en voorwaardelijke opmaak  (gelezen 2118 keer)

0 leden en 1 gast bekijken dit topic.

Offline BlackDevil

  • Ambassadeur
  • *****
  • Berichten: 1.029
  • Geslacht: Vrouw
  • veni vidi vici
VBA en voorwaardelijke opmaak
« Gepost op: 03 april 2021, 12:51:43 »
Hallokes allen,

Ik ben alweer enkele weken met een VBA-projectje bezig en dit gaat allemaal redelijk vlotjes, heb al héél veel bijgeleerd inzake VBA maar nu is er een kleinigheidje waar ik toch wel wat hulp bij kan gebruiken.
Ik wil namelijk in mijn tabel, zodra alle cellen binnen éénzelfde rij ingevuld zijn, de rij door VBA laten inkleuren met een cyaan-achtergrondkleur om een beter en sneller zicht te hebben van welke personen ik alle gegevens heb (de tabel zal uiteindelijk meer dan 100000 rijen bevatten).
De tabel heeft 18 kolommen en de eerste rij met gegevens is rij 3 (rij 2 zijn de kolomnamen en in rij 1 staan mijn macro-knoppen).

Kan iemand mij helpen met de code hiervoor?

Alvast dank op voorhand.

groetjes,
BlackDevil
1) HP ProDesk (Windows 10 Pro Edu / Intel(R) Core(TM) i5-7500 CPU@ 3.40Ghz 3.41Ghz)
2) Laptop ASUS (Windows 10 Home / Intel(R) Core(TM) i3-3110M CPU@ 2.40GHz 2.40GHz)
Beide (RAM : 8,00 GB / 64-bits besturingssysteem, x64-processor)
Extern (Seagate Backup+ Hub 6TB / Seagate BUP Slim 2TB / Seagate Expansion Desk 5TB / HP ENVY 6030e)
Bullguard Premium Protection / Office Pro Plus 2016 NL

Offline Montagnard

  • Ambassadeur
  • *****
  • Berichten: 2.385
  • Geslacht: Man
  • To Windows or not to Windows , that's the question
Re: VBA en voorwaardelijke opmaak
« Reactie #1 Gepost op: 03 april 2021, 14:15:06 »
zoals steeds : graag een voorbeeld bestand bijvoegen , haal er de gevoelige gegevens uit weg of vervang die door dummy's , is voor ons helpers heel moeilijk om zonder voorbeeld te visualiseren wat de vraagsteller juist bedoeld.
je kan in het bestand eventueel ook de nodig info bijzetten van wat je precies wilt...

Mvg,
Montagnard.

HP ProBook 450 G3 15 inch
OS : Windows 11 Pro NLD 64bit
Intel(R) Core(TM) i5-6200U CPU @ 2.30GHz
16 GB  Vengeance 2666 MHz
KINGSTON SA400S37480G - 447,13 GB als bootdisk
WD Blue Solid state 2 TB - intern - M.2 2280 - SATA als datadisk
AV: Windows Defender
FW: Windows Firewal

Offline SoftAid

  • Administrator
  • Ambassadeur
  • *****
  • Berichten: 20.128
  • Geslacht: Man
  • Nobody is perfect, not even me...
Re: VBA en voorwaardelijke opmaak
« Reactie #2 Gepost op: 03 april 2021, 14:32:20 »
Hallo BlackDevil, ;D

Is er een kolom die steeds voltooid is, waar de definitieve laatste rij van bekend is?

Dit werkt niet als je laatste rij niet bekend is, maar je kan er op verder borduren (is ook een hobby  ;D)
Er zitten nog foutjes in, maar dan heb jij ook nog wat te doen  ;D
Sub kleurenAls()
    Dim Rng As Long, totaal As Long, ws As Worksheet
   
    Set ws = Worksheets("Blad1")
    totaal = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row    'bereken de laatste rij in "C" 'Dit mag ook "A" zijn
    Rng = Range("C2:C" & totaal).SpecialCells(xlCellTypeBlanks).Count ' Tel de lege cellen in C2:C totaal
    MsgBox "aantal lege cellen =" & " " & Rng
    If Rng = 1 Then   'foutmelding als Rng = 0
        Range("C3:C" & totaal).Interior.ColorIndex = 27 'Daarom C2 ongebruikt gelaten
    End If

End Sub


:) SoftAid :)             
Maximum grootte bijlagen vergroot naar 4 MB
Dubbelposten, het posten op verschillende forums van dezelfde vraag, dient op
voorhand gemeld te worden, met een link naar het topic op de andere site.
Overtreding van deze regel kan bestraft worden met verbanning !

Offline BlackDevil

  • Ambassadeur
  • *****
  • Berichten: 1.029
  • Geslacht: Vrouw
  • veni vidi vici
Re: VBA en voorwaardelijke opmaak
« Reactie #3 Gepost op: 03 april 2021, 14:44:31 »
Hey Montagnard,

Mijn excuses, gezien het een redelijk algemene vraag betreft dacht ik niet dat een voorbeeldbestand nodig zou zijn.
Ik heb een tabel met 18 kolommen.
Zolang er rijen zijn waar in één of meerdere cellen geen waarde is ingevuld (dus leeg is) moet er niets gebeuren.
Enkel zodra alle cellen in een rij ingevuld zijn dient de rij ingekleurd te worden.
De rijen worden gevuld via vba met gegevens uit andere werkbladen.
Ik wil gewoon een beter zicht krijgen voor welke rijen er nog gegevens ontbreken.

Mijn bestand gaan strippen van gevoelige informatie is niet haalbaar en mijn bestand omvat ook gigantisch veel vba-code om alles
werkende te houden. Veel code is ook afhankelijk van die specifieke informatie.
Maar ik heb even een leeg bestandje aangemaakt om het 'duidelijk' te maken.

@SoftAid,

Ja, de eerste 5 en laatste 2 kolommen zijn sowieso altijd ingevuld.
Ik zal jouw code eens bestuderen en kijken of ik ermee verder kan (denk het wel).
Laat wel weten of het al dan niet lukt.
*Edit* oops, te snel gelezen... nee de laatste rij is niet bekend... er komen dagelijks tientallen en soms honderden rijen bij...

groetjes,
BlackDevil

1) HP ProDesk (Windows 10 Pro Edu / Intel(R) Core(TM) i5-7500 CPU@ 3.40Ghz 3.41Ghz)
2) Laptop ASUS (Windows 10 Home / Intel(R) Core(TM) i3-3110M CPU@ 2.40GHz 2.40GHz)
Beide (RAM : 8,00 GB / 64-bits besturingssysteem, x64-processor)
Extern (Seagate Backup+ Hub 6TB / Seagate BUP Slim 2TB / Seagate Expansion Desk 5TB / HP ENVY 6030e)
Bullguard Premium Protection / Office Pro Plus 2016 NL

Offline Montagnard

  • Ambassadeur
  • *****
  • Berichten: 2.385
  • Geslacht: Man
  • To Windows or not to Windows , that's the question
Re: VBA en voorwaardelijke opmaak
« Reactie #4 Gepost op: 03 april 2021, 14:52:59 »
kunnen we al meer mee...
nu volgende vraag : moet de kleuring instant gebeuren als ergens willekeurig de laatste lege cel in een rij is ingevuld ?
Of worden de cellen in een rij in volgorde ingevuld (van kolom A naar kolom R) , want dan moet er énkel gecheckt worden als de cel R in een rij is ingevuld ?
anders zal er continue moeten gecheckt worden...

Mvg,
Montagnard.
HP ProBook 450 G3 15 inch
OS : Windows 11 Pro NLD 64bit
Intel(R) Core(TM) i5-6200U CPU @ 2.30GHz
16 GB  Vengeance 2666 MHz
KINGSTON SA400S37480G - 447,13 GB als bootdisk
WD Blue Solid state 2 TB - intern - M.2 2280 - SATA als datadisk
AV: Windows Defender
FW: Windows Firewal

Offline BlackDevil

  • Ambassadeur
  • *****
  • Berichten: 1.029
  • Geslacht: Vrouw
  • veni vidi vici
Re: VBA en voorwaardelijke opmaak
« Reactie #5 Gepost op: 03 april 2021, 15:01:37 »
Hey Montagnard,

De cellen worden willekeurig ingevuld...
Er is dus geen vaste volgorde.

groetjes,
BlackDevil
1) HP ProDesk (Windows 10 Pro Edu / Intel(R) Core(TM) i5-7500 CPU@ 3.40Ghz 3.41Ghz)
2) Laptop ASUS (Windows 10 Home / Intel(R) Core(TM) i3-3110M CPU@ 2.40GHz 2.40GHz)
Beide (RAM : 8,00 GB / 64-bits besturingssysteem, x64-processor)
Extern (Seagate Backup+ Hub 6TB / Seagate BUP Slim 2TB / Seagate Expansion Desk 5TB / HP ENVY 6030e)
Bullguard Premium Protection / Office Pro Plus 2016 NL

Offline Montagnard

  • Ambassadeur
  • *****
  • Berichten: 2.385
  • Geslacht: Man
  • To Windows or not to Windows , that's the question
Re: VBA en voorwaardelijke opmaak
« Reactie #6 Gepost op: 03 april 2021, 15:29:29 »
mja , dus moet er continue gecontroleerd worden op (volgens U aangeeft in Uw 1ste post) 100000 rijen (of meer) x 18 kolommen ?
dus continue van iedere rij de 18 kolommen overlopen of ze al dan niet zijn ingevuld en als ze allemaal in die rij zijn ingevuld die 18 cellen dan inkleuren ?

kan het dat er ook een cel is ingevuld in laat ons zeggen rij 98000 of lopen de rijen stelselmatig vol ?
mag het ook via een opdrachtknop of bij de start van het "programma" ?

Mvg,
Montagnard.

HP ProBook 450 G3 15 inch
OS : Windows 11 Pro NLD 64bit
Intel(R) Core(TM) i5-6200U CPU @ 2.30GHz
16 GB  Vengeance 2666 MHz
KINGSTON SA400S37480G - 447,13 GB als bootdisk
WD Blue Solid state 2 TB - intern - M.2 2280 - SATA als datadisk
AV: Windows Defender
FW: Windows Firewal

Offline BlackDevil

  • Ambassadeur
  • *****
  • Berichten: 1.029
  • Geslacht: Vrouw
  • veni vidi vici
Re: VBA en voorwaardelijke opmaak
« Reactie #7 Gepost op: 03 april 2021, 15:57:37 »
Hey Montagnard,

Mijn excuses, ben niet helemaal duidelijk geweest.
De code moet enkel lopen nadat de tabel word bijgewerkt, de bijwerking gebeurd telkens via een opdrachtknop dus ik wou de inkleurcode gewoon toevoegen aan de bestaande opdrachtknop na de code om de tabel bij te werken.

Dit is mijn bijwerk-code van de tabel :
Sub geboorten()

Application.ScreenUpdating = False
With Sheets("INDEX_DA-GA")
    rijen = .Cells(Rows.Count, 1).End(xlUp).Row
    For g = 2 To rijen
        If .Cells(g, 14) = "" Then
        gslcht = .Cells(g, 1)
        stamnaam = .Cells(g, 2)
        aktenaam = .Cells(g, 3)
        voornaam = .Cells(g, 4)
        g_dat = .Cells(g, 5) & "-" & .Cells(g, 6) & "-" & .Cells(g, 7)
        g_jaar = .Cells(g, 7)
        g_plaats = .Cells(g, 8)
        v_vrnm = .Cells(g, 9)
        m_naam = .Cells(g, 10)
        m_vrnm = .Cells(g, 11)
        ouders = .Cells(g, 12)
        gezin = .Cells(g, 13)
       
        With Sheets("TOTAAL-DB")
        rijen = .Cells(Rows.Count, 1).End(xlUp).Row
        gevonden = False
        For rij = 2 To rijen
            If .Cells(rij, 5) = voornaam And .Cells(rij, 17) = ouders And (.Cells(rij, 6) = "" Or .Cells(rij, 6) = 0) Then
            gevonden = True
            h_dat = .Cells(rij, 8)
            If h_dat <> "" Then h_jaar = CDbl(Right(h_dat, 4))
            If h_jaar = "" Then h_jaar = 0
            o_dat = .Cells(rij, 10)
            If o_dat <> "" Then o_jaar = CDbl(Right(o_dat, 4))
            If o_jaar = "" Then o_jaar = 0
            If (h_jaar > 0 And g_jaar < h_jaar) Or (o_jaar > 0 And g_jaar <= o_jaar) Then
            .Cells(rij, 6) = g_dat
            .Cells(rij, 7) = g_plaats
            End If
            End If
        Next rij
        If gevonden = False Then
        rij = rijen + 1
        .Cells(rij, 1) = gslcht
        .Cells(rij, 2) = stamnaam
        .Cells(rij, 3) = aktenaam
        .Cells(rij, 5) = voornaam
        .Cells(rij, 6) = g_dat
        .Cells(rij, 7) = g_plaats
        .Cells(rij, 12) = v_vrnm
        .Cells(rij, 13) = m_naam
        .Cells(rij, 14) = m_vrnm
        .Cells(rij, 17) = ouders
        .Cells(rij, 18) = gezin
        End If
        End With
        .Cells(g, 14) = "OK"
        End If
    Next g
End With
End Sub

groetjes,
BlackDevil
1) HP ProDesk (Windows 10 Pro Edu / Intel(R) Core(TM) i5-7500 CPU@ 3.40Ghz 3.41Ghz)
2) Laptop ASUS (Windows 10 Home / Intel(R) Core(TM) i3-3110M CPU@ 2.40GHz 2.40GHz)
Beide (RAM : 8,00 GB / 64-bits besturingssysteem, x64-processor)
Extern (Seagate Backup+ Hub 6TB / Seagate BUP Slim 2TB / Seagate Expansion Desk 5TB / HP ENVY 6030e)
Bullguard Premium Protection / Office Pro Plus 2016 NL

Offline AD1957

  • Ervaren lid
  • ***
  • Berichten: 250
  • Oplossing.be
Re: VBA en voorwaardelijke opmaak
« Reactie #8 Gepost op: 03 april 2021, 16:34:20 »
Probeer het eens zo.
totaal = ActiveSheet.Cells(Rows.Count, "E").End(xlUp).Row
 
 Range("A" & i & ":R" & i).Interior.Color = xlNone
 
 For i = 2 To totaal
    Teller = Range("E" & i & ":P" & i).SpecialCells(2).Count

    If Teller < 12 Then
         Range("A" & i & ":R" & i).Interior.ColorIndex = 27
    End If
 Next i
Groet,

Albert

Offline SoftAid

  • Administrator
  • Ambassadeur
  • *****
  • Berichten: 20.128
  • Geslacht: Man
  • Nobody is perfect, not even me...
Re: VBA en voorwaardelijke opmaak
« Reactie #9 Gepost op: 03 april 2021, 16:46:05 »
Enkel zodra alle cellen in een rij ingevuld zijn dient de rij ingekleurd te worden.
.....
Ik wil gewoon een beter zicht krijgen voor welke rijen er nog gegevens ontbreken.
Je haalt rijen en kolommen door elkaar, maar het probleem blijft hetzelfde:
Als je niet weet waar je rij (of kolom) eindigt kan je toch niet bepalen of er lege cellen zijn.
Dat kan toch enkel als je gebied afgebakend is (de laatste cel bekend is, wat volgens jou niet is)
*Edit* oops, te snel gelezen... nee de laatste rij is niet bekend... er komen dagelijks tientallen en soms honderden rijen bij...
Als er dus 100000 rijen ingevuld zijn, dan telt de 100.001 rij als een lege cel, en werkt de code niet.
Dan lukt het met geen enkele code.
Mogelijk kan je met voorwaardelijke opmaak de niet-lege cellen individueel kleuren, dan kan je snel door een rij gaan en zien welke cellen nog leeg zijn (niet gekleurd zijn)

:) SoftAid :)             

Maximum grootte bijlagen vergroot naar 4 MB
Dubbelposten, het posten op verschillende forums van dezelfde vraag, dient op
voorhand gemeld te worden, met een link naar het topic op de andere site.
Overtreding van deze regel kan bestraft worden met verbanning !

Offline BlackDevil

  • Ambassadeur
  • *****
  • Berichten: 1.029
  • Geslacht: Vrouw
  • veni vidi vici
Re: VBA en voorwaardelijke opmaak
« Reactie #10 Gepost op: 03 april 2021, 17:46:03 »
Probeer het eens zo.
totaal = ActiveSheet.Cells(Rows.Count, "E").End(xlUp).Row
 
 Range("A" & i & ":R" & i).Interior.Color = xlNone
 
 For i = 2 To totaal
    Teller = Range("E" & i & ":P" & i).SpecialCells(2).Count

    If Teller < 12 Then
         Range("A" & i & ":R" & i).Interior.ColorIndex = 27
    End If
 Next i

Hey Albert,

Mits 1 tekentje te wijzigen werkt jouw code perfect  :thumbsup:
Heb er wel ipv "If teller < 12" dus If teller = 12" moeten van maken omdat hij anders de rijen kleurde die wél lege cellen bevatte en
hij moest dus de rijen kleuren die géén lege cellen bevatten.

Maar het werkt dus, enorm bedankt!

Opgelost dus ;-).

@SoftAid,

Mijn excuses, ik was duidelijk wat verward (met allerlei tegelijk bezig :D)...
Maar Albert heeft het dus opgelost gekregen  ;)

groetjes,
BlackDevil
1) HP ProDesk (Windows 10 Pro Edu / Intel(R) Core(TM) i5-7500 CPU@ 3.40Ghz 3.41Ghz)
2) Laptop ASUS (Windows 10 Home / Intel(R) Core(TM) i3-3110M CPU@ 2.40GHz 2.40GHz)
Beide (RAM : 8,00 GB / 64-bits besturingssysteem, x64-processor)
Extern (Seagate Backup+ Hub 6TB / Seagate BUP Slim 2TB / Seagate Expansion Desk 5TB / HP ENVY 6030e)
Bullguard Premium Protection / Office Pro Plus 2016 NL

Offline SoftAid

  • Administrator
  • Ambassadeur
  • *****
  • Berichten: 20.128
  • Geslacht: Man
  • Nobody is perfect, not even me...
Re: VBA en voorwaardelijke opmaak
« Reactie #11 Gepost op: 03 april 2021, 18:03:11 »
Probeer het eens zo.
totaal = ActiveSheet.Cells(Rows.Count, "E").End(xlUp).Row

Hallo Albert,

sorry van dit vraagje van een dom blondje (dom grijsje  ;D) maar hoe kan je met end.xlUp.Row bepalen of een kolom (of rij) volledig is (en data bevat) als de geen "eind-referentie" hebt, er kunnen steeds (lege) rijen bijkomen....  Of mis ik iets in de redenering?

:) SoftAid :)             


 
Maximum grootte bijlagen vergroot naar 4 MB
Dubbelposten, het posten op verschillende forums van dezelfde vraag, dient op
voorhand gemeld te worden, met een link naar het topic op de andere site.
Overtreding van deze regel kan bestraft worden met verbanning !

Offline AD1957

  • Ervaren lid
  • ***
  • Berichten: 250
  • Oplossing.be
Re: VBA en voorwaardelijke opmaak
« Reactie #12 Gepost op: 03 april 2021, 18:19:42 »

@SoftAid,

Citaat
Ja, de eerste 5 en laatste 2 kolommen zijn sowieso altijd ingevuld.

Ik bepaal de laatste rij van kolom E, kan natuurlijk ook kolom a.b.c.d.,q of r zijn.

Of heb ik het hier mis? ;)
Groet,

Albert

Offline SoftAid

  • Administrator
  • Ambassadeur
  • *****
  • Berichten: 20.128
  • Geslacht: Man
  • Nobody is perfect, not even me...
Re: VBA en voorwaardelijke opmaak
« Reactie #13 Gepost op: 03 april 2021, 18:23:52 »
Hallo Albert,

akkoord als dat nog geldig was, maar daar is TS van terug gekomen...

Citaat
Ja, de eerste 5 en laatste 2 kolommen zijn sowieso altijd ingevuld.
Ik zal jouw code eens bestuderen en kijken of ik ermee verder kan (denk het wel).
Laat wel weten of het al dan niet lukt.
*Edit* oops, te snel gelezen... nee de laatste rij is niet bekend... er komen dagelijks tientallen en soms honderden rijen bij...

Sorry....

:) SoftAid :)             

Maximum grootte bijlagen vergroot naar 4 MB
Dubbelposten, het posten op verschillende forums van dezelfde vraag, dient op
voorhand gemeld te worden, met een link naar het topic op de andere site.
Overtreding van deze regel kan bestraft worden met verbanning !

Offline AD1957

  • Ervaren lid
  • ***
  • Berichten: 250
  • Oplossing.be
Re: VBA en voorwaardelijke opmaak
« Reactie #14 Gepost op: 03 april 2021, 18:26:25 »
Afwachten maar.
Groet,

Albert

 


www.combell.com