Overrulen van voorwaardelijke opmaak

Als in Excel de achtergrond van een cel ingekleurd is met voorwaardelijke opmaak dan zal deze kleur niet veranderen als met behulp van celeigenschappen een andere kleur wordt opgegeven. De kleur die is ingesteld met voorwaardelijke opmaak heeft altijd voorrang. Maar toch kan het soms gewenst zijn om een cel handmatig een afwijkende kleur te geven ongeacht de instelling van voorwaardelijke opmaak. Om dat te realiseren zal gebruik gemaakt moeten worden van VBA.

Open met Alt+F11 het VBA-editor scherm en zet de volgende functie in een willekeurige module (als nog geen module aanwezig is voeg dan eerst een module toe via Invoegen -> Module):

Function CellFilled(rng As Range) As Boolean
    CellFilled = (Range(rng.Address).Interior.Pattern <> xlNone)
End Function

Met deze functie wordt gecheckt of een cel een ingestelde achtergrondkleur heeft (dus niet de celkleur met voorwaardelijke opmaak). Selecteer vervolgens het bereik waarvoor de handmatige overruling moet gelden en voeg aan de voorwaardelijke opmaak een nieuwe regel toe op basis van een formule en zet daarin de volgende formule:

=cellfilled(A1)

Cel A1 moet daarbij de eerste cel zijn uit het geselecteerde bereik. Er moet voor deze regel verder geen opmaak worden ingesteld. Zorg er daarbij voor dat dit de eerste regel is die toegepast wordt en vink de checkbox aan ‘stoppen indien waar’.  Hierbij een voorbeeld hoe het scherm van voorwaardelijke opmaak er dan uit kan zien:

overrulen_voorwaardelijke_opmaak1_755x305

Maar dit is nog niet voldoende om dit goed te laten werken. Het wijzigen van een achtergrondkleur genereert geen gebeurtenis (eventueel kan hiervoor een specifieke gebeurtenis voor worden aangemaakt, zie: maken van CellColorChange-event) en ook het opnieuw laten berekenen van het werkboek zorgt er niet voor dat de handmatig ingestelde kleur zichtbaar wordt. Deze kleur wordt alleen zichtbaar na een volledige hercalculatie (Ctrl+Alt+F9) van het werkboek. Handmatig deze toetsen indrukken na elke kleurwijziging is natuurlijk geen optie, daarom wordt gebruik gemaakt van het Selection_Change-event. Kies daarbij in de VBA-editor het betreffende werkblad en voeg daar de volgende code toe:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.CalculateFull
End Sub

Daarmee wordt de kleur zichtbaar als na het instellen van de kleur een andere willekeurige cel wordt geselecteerd. Een nadeel hiervan is dat bij elke celselectie-wijziging een volledige hercalculatie doorgevoerd wordt. Bij eenvoudige werkboeken is dat geen probleem, maar bij complexe werkboeken met veel formules kan dat vertragend werken. Dan kan beter een aangepaste code gebruikt worden. Als bijvoorbeeld de voorwaardelijke opmaak geldt voor A1:A10 dan kan ingesteld worden dat de volledige calculatie alleen plaatsvindt als een cel in dat bereik of aangrenzende cel wordt geselecteerd:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Range("A1:B11")) Is Nothing Then
        Application.CalculateFull
    End If
End Sub

Als iemand de celkleur wijzigt en vervolgens direct een ander tabblad wordt selecteert, dan vindt de selection_change-gebeurtenis niet plaats. Door het toevoegen van de volgende code worden altijd de ingestelde kleuren weergegeven als het betreffende werkblad wordt geopend:

Private Sub Worksheet_Activate()
    Application.CalculateFull
End Sub

Op deze manier is het mogelijk om de ingestelde voorwaardelijke opmaak handmatig te overrulen. Het bestand moet daarna natuurlijk wel opgeslagen worden met ingeschakelde macro’s.

 

Interesse in andere tips & trucs? Ga dan naar ons tips&trucs-overzicht of neem een kijkje op onze pagina met tutorials.

Reacties op onze tutorials en tips&trucs zijn van harte welkom. Dat kan door hieronder een reactie toe te voegen of door rechtstreeks contact met ons op te nemen.

Geef een reactie

Het e-mailadres wordt niet gepubliceerd.