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
VBA
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:
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 gebeurtenis) 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.
Vragen / suggesties
Hopelijk heeft dit artikel geholpen bij het overrulen van voorwaardelijke opmaak in Excel. Als er verdere vragen over dit onderwerp zijn of suggesties voor verbetering, plaats dan een reactie hieronder.