Bereik Voorwaardelijke Opmaak VBA

[Ivanhoes]

Hoi mensen,

Wat is de code bij FormatConditions om een range op te geven van welke cellen
er gekleurd moeten worden?

Binnen onderstaande code wordt de range ("A120") opgegeven. Die is voor het opgeven
van de range waarbinnen de voorwaardelijke opmaak moet "kijken". Dat is mij duidelijk.

Ik wil echter dat als binnen die range in een cel wordt voldaan aan "WEL", in diezelfde rij de
kolommen C t/m E gekleurd worden.
Zo wil ik ook dat als in een cel binnen die range ("A120"), op een willekeurige regel "NIET"
voorkomt, in diezelfde regel de kolommen G t/m H gekleurd worden.

Ik kom er maar niet achter waar ik die tweede range moet plaatsen. Ik wil ook niet de gewone
Voorwaardelijke Opmaak gebruiken, omdat in het oorspronkelijke document veel geknipt en
geplakt wordt in het werkblad.

Als het nodig is plaats ik uiteraard nog een bestandje, maar ik denk het een kleinigheidje is
waar ik met mijn beginnerskennis VBA net niet uitkom.

Ik hoop dat iemand mij kan helpen.

Groetjes,

Ivanhoes.

Sub Voorw_Opmaak()
With Range("A1:D20")

.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="WEL"
.FormatConditions(1).Interior.ColorIndex = 3

.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="NIET"
.FormatConditions(2).Interior.ColorIndex = 6

.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="NVT"
.FormatConditions(3).Interior.ColorIndex = 8

End With
End Sub

 

[MarcelBeug]

Je moet niet de range opgeven waarnaar gekeken moet worden, maar de range die voorwaardelijk opgemaakt moet worden.
En dan zorgen dat de formule naar de juiste range kijkt.

Edit: en dan heb je ook een ander type nodig: xlExpression

 

[Ivanhoes]

Hoi MarcelBeug,

Bedankt voor je reactie.

Maar nu ben ik er nog niet. Ik weet dat nu dat ik xlCellValue moet veranderen in xlExpression.

Laat ik even uitgaan van het voorbeeld "WEL" in cel A1. In dat geval is de range waarnaar
gekeken moet worden, en dus waarop Voorwaardelijk Opmaak toegepast moet worden: ("C1:E1").

Hoe zorg ik er dan voor dat, als de waarde "WEL" in de range ("A120") voorkomt (in dit voorbeeld dus in cel A1),
de formule naar de range ("C1:E1") gaat zoeken?

 

[zapatr]

Ik wil echter dat als binnen die range in een cel wordt voldaan aan "WEL", in diezelfde rij de kolommen C t/m E gekleurd worden. Zo wil ik ook dat als in een cel binnen die range ("A1: D20"), op een willekeurige regel "NIET" voorkomt, in diezelfde regel de kolommen G t/m H gekleurd worden.

En wat moet er gebeuren als in eenzelfde rij ZOWEL "wel" als "niet" voorkomt?

 

[MarcelBeug]

Kortom, het is geen kleinigheidje. Tijd voor een voorbeeldbestandje.
Overigens zal de formule ergens naar A1: D20 moeten gaan zoeken en niet andersom.

Edit: wat bedoel je eigenlijk met dat je niet de gewone voorwaardelijke opmaak wilt gebruiken?
VBA levert ook gewone voorwaardelijke opmaak op.

 

[zapatr]

Wellicht bedoelt de vragensteller dit:

Sub Macro2()
With Sheets("Blad1")
.Range("C1:H20").FormatConditions.Delete
With .Range("C1:E20")
.FormatConditions.Add Type:=xlExpression, Formula1:="=aantal.als($a1:$d1;""wel"")>0"
.FormatConditions(1).Interior.ColorIndex = 6
End With
With .Range("G1:H20")
.FormatConditions.Add Type:=xlExpression, Formula1:="=aantal.als($a1:$d1;""niet"")>0"
.FormatConditions(1).Interior.ColorIndex = 8
End With
End With
End Sub

 

[Ivanhoes]

Hoi mensen,

Allen bedankt voor jullie reacties. Tijd voor inderdaad een voorbeeldbestandje.

In het bestandje staan 2 tabbladen. Eén met de code van Zapatr en een
zoals het zou moeten worden.

Een aantal opmerkingen:
- Op elke rij kan een waarde maar 1 keer voorkomen. In dezelfde rij komt dus nooit "Wel" en "Niet" voor.
- Je hebt ook voorwaardelijke opmaak in de betekenis van Nederlands, maar ik bedoel de voorwaardelijke
opmaak zoals die bedoeld is in Excel. (Daar zit verschil in!) Dus FormatConditions. Als "Wel" wordt weggehaald,
moet de opmaak verwijderd worden.
- @Zapatr: jouw code komt heel dicht in de buurt. Alleen wil ik dat de cellen de opmaak krijgen op de betreffende
regel waar de waarde "Wel" of "Niet" wordt gevonden en dat deze cellen die kleuren behouden als er ergens anders
op een rij wéér "Wel" of "Niet" wordt gevonden.

Hoe dan ook, de code van Zapatr zit al dicht in de buurt. Voor de duidelijkheid dus nog een voorbeeldbestandje meegestuurd.

Alvast bedankt en groetjes,

Ivanhoes.Bekijk bijlage Voorwaardelijke opmaak VBA.xlsm

 

[zapatr]

Mijn macro hierboven doet precies wat je in je eerste bericht vroeg, maar in je laatste bericht heb je de bereiken aangepast. En je laat daarbij de kolommen die je wil kleuren elkaar overlappen, dat gaat niet met voorw. opmaak in 1 macro. Je zult dus die bereiken E1:G20, F1:H20, en G1:J20 moeten aanpassen of een ander soort macro gebruiken.
Overigens vraag ik me af wat je met de macro wil bereiken. Je wil voorw. opmaak toepassen, maar - als ik het goed begrijp - ook weer uitschakelen. Ik vind het een dubieuze handeling. Maar als je een macro wil gebruiken en beslist de bereiken wik kleuren die je in je laatste bericht aangaf, dan kun je onderstaande macro gebruiken, behorend bij het blad waarin je gegevens staan:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("a1:d20"), Target) Is Nothing Then
Dim n As Integer, nv As Integer, x As Integer, w As Integer
Range("e1:j20").Interior.ColorIndex = -4142
n = 0: nv = 0: w = 0
For x = 1 To 20
w = WorksheetFunction.CountIf(Range("a" & x & ":d" & x), "wel")
n = WorksheetFunction.CountIf(Range("a" & x & ":d" & x), "niet")
nv = WorksheetFunction.CountIf(Range("a" & x & ":d" & x), "NVT")
If w > 0 Then Range("e" & x & ":" & "g" & x).Interior.ColorIndex = 3
If n > 0 Then Range("f" & x & ":" & "h" & x).Interior.ColorIndex = 6
If nv > 0 Then Range("g" & x & ":" & "j" & x).Interior.ColorIndex = 7
Next x
End If
End Sub

 

[zapatr]

Let op dat de macro hierboven enkele minuten na plaatsing een kleine maar noodzakelijke wijziging onderging.

 

[HSV]

Het bereik verspringt doordat de rij relatief is in A1048576: D1048576.

Dit is in mijn beleving alleen op te lossen door 'select' en 'selection'.
Voorwaardelijke opmaak is voorwaardelijke opmaak is voorwaardelijke opmaak is formatconditons.
Maar waarom moet dat steeds met Vba erin gezet worden.
Bij elke wijziging verwijderd het de VW opmaak en maakt nieuwe aan (onzin in mijn optiek).

Private Sub Worksheet_Change(ByVal Target As Range)
Range("E1:J20").FormatConditions.Delete
Set trg = Target
 
 Range("E1:G20").Select
 With Selection.FormatConditions.Add(xlExpression, , "=aantal.als($a1:$d1;""nvt"")>0")
   .Interior.ColorIndex = 3
   .StopIfTrue = False
 End With
 
 Range("F1:H20").Select
 With Selection.FormatConditions.Add(xlExpression, , "=aantal.als($a1:$d1;""wel"")>0")
   .Interior.ColorIndex = 6
   .StopIfTrue = False
  End With
 
 Range("H1:J20").Select
 With Selection.FormatConditions.Add(xlExpression, , "=aantal.als($a1:$d1;""niet"")>0")
   .Interior.ColorIndex = 25
   .StopIfTrue = False
End With
   Application.Goto trg
End Sub

Edit: Dat ik 'select' en 'selection' moet gebruiken is een bug in Excel 2007.
Vanaf 2010 werk dit zonder.

 

[Ivanhoes]

Hoi mensen,

Allen bedankt voor de reacties!

Zowel de code van HSV, als die van Zpatar werken zoals het moet. De vraag is hiermee opgelost.

De code van HSV is dategene waar ik exact om vroeg, namelijk FormatConditions.
De code van Zpatar echter werkt iets lekkerder: het gebeurt naadloos, terwijl die van HSV een hele
kleine flikkering in het scherm laat zien. Een kniesoor die daar op let...

Ik kan dus beide codes gebruiken. Belangrijker voor mij was echter óók om meer te leren over het gebruik
van de range in de FormatConditions. Dat is nu gelukt!

Dus nogmaals: Bedankt!

Groetjes,

Ivanhoes.

 

[HSV]

Flikkering uitschakelen:
Onder de private sub.

application.screenupdating = false

 

[zapatr]

@Ivanhoes,
Ik blijf mij afvragen (welke code je ook gebruikt) hoe je de macro denkt te gaan gebruiken.
Kun je daar iets over zeggen?

 

[Ivanhoes]

Hoi mensen,

Ik zag dat ik vergeten was de vraag op opgelost te zetten. Dat is maar goed ook,
want ik zag nog 2 nagekomen reacties.

@HSV: Stom van mij, want Screenupdating wist ik gewoon

@Zpatar:
Dit is een iets langer verhaal. In het oorspronkelijke bestand wordt er binnen het bedoelde
bereik veel geplakt/gekopieerd/geknipt. Als je dan gebruikt maakt van de ingebouwde
Voorwaardelijke Opmaak van Excel zelf, zie je dat de bereiken steeds groter worden. Maw: Het kopiëren
en knippen zorgt voor onnodig veel overlappende bereiken binnen VO, omdat deze "gewoon" meegenomen worden.

In VBA heb je daar geen last van. Bovendien (en dat was óók belangrijk) als je dan gaat kopiëren en plakken,
wordt de opmaak NIET meegenomen. Vandaar dat ik specifiek om FormatConditions vroeg.

Het is dus niet zo dat ik VO enerzijds wil inschakelen en daarna weer wil uitschakelen. Het ging mij om het
kopiëren van de opmaak. Dit wilde ik niet mee laten nemen als er ergens anders geplakt wordt.

***********

Dan nog even terugkomen op HSV: Ik zag het volgende:

Edit: Dat ik 'select' en 'selection' moet gebruiken is een bug in Excel 2007. Vanaf 2010 werk dit zonder.

Hoe is de code dan zonder Select en Selection? Wat moet hiervoor in de plaats komen?
Ik vraag dat omdat, na testen, de Select methode voor een fout zorgt. Dat heeft niets met de codes in dit topic te maken,
maar alles met een andere macro die in werking wordt gezet dmv een knop. Deze laatste code gebruikt óók een aantal
keer Select en dat kan in mijn ogen natuurlijk niet tegelijkertijd (Had ik even niet over nagedacht).

Nou, alvast bedankt weer en groetjes,

Ivanhoes.

 

[HSV]

En wordt het bereik dan nu niet vergroot met kopiëren en plakken?
Je hebt namelijk d.m.v. Vba gewoon voorwaardelijke opmaak (formatconditions) verkregen.

De code voor of na Excel 2007 wordt dan:

 With Range("E1:G20").FormatConditions.Add(xlExpression, , "=aantal.als($a1:$d1;""nvt"")>0")

 

[Ivanhoes]

Hoi HSV,

Bedankt voor de code-anders-dan-2007!

En ja......je hebt helemaal gelijk....In de voorwaardelijke opmaak komen er nu ook steeds meer regels bij.

Dan wordt het tóch gewone opmaak en moet ik maar accepteren dat de opmaak ook gekopieerd wordt.

Ik ben (nog) niet zo bekend met VBA en zou dus niet weten of er andere oplossingen zijn.

Groetjes,

Ivanhoes.

 

[HSV]

En plakken speciaal is geen optie?

 

[Ivanhoes]

Plakken Speciaal is normaal gesproken dé optie....maar ik ben niet degene die knipt en plakt.

En dan denk je dus gebruik te kunnen maken van FormatConditions (want de opmaak wordt niet gekopieerd).
Ik zal iets anders moeten verzinnen...of toch op de oude manier verder moeten gaan.

Aan de andere kant: Er is natuurlijk een optie om vóórdat het bestand geopend wordt alle VO in het tabblad te verwijderen.
Niet ideaal, maar het voorkomt een steeds trager wordende VO.

en de code zou dan moeten luiden:

Range("A1:D20").FormatConditions.Delete

Range misschien aanpassen, maar als ik het goed begrijp doet deze code de VO verwijderen?

 

[HSV]

Dan kan je onderstaande code in Thisworkbook zetten.
Het verwijderd alle voorwaardelijke opmaak van het eerste blad.

Private Sub Workbook_Open()
 Sheets(1).Cells.FormatConditions.Delete
End Sub

 

[Ivanhoes]

HSV: bedankt voor je meedenken.
Dit is een goede optie om elke keer de VO te verwijderen vóórdat het bestand geopend wordt.

Nu maak ik echter een foutje (dat gebeurt me wel meer)....

Het is mooi dat de VO verwijderd wordt vóór, of tijdens, het openen van het bestand, maar ik bedoel eigenlijk
voordat het betreffende tabblad geopend wordt. Ik neem aan dat in VBA voor het betreffende blad gekozen
wordt voor "Worksheet" en "Activate" en dan de code die je net opgaf:

Private Sub Workbook_Activate()
 Sheets(1).Cells.FormatConditions.Delete
End Sub