excel - 2016 - cel in kleuren

[paco]

Heb in excel een data bestand van een 8000 records. Elk record bestaat uit verschillen kolommen met gegevens. In één van de kolommen heb ik een oplijsting van het fotonummer dat bij dit record hoort.
Heb de inhoud van mijn directory waar mijn foto's instaan, geconcipieerd - via het progje printfolder - eveneens naar een excel bestand. 
Heb met de formule "vergelijken" het record gevonden waar de betroffen foton° staat. Nu zou ik elke cel waarvan ik een foto heb een achtergrond kleur willen geven - maar natuurlijk niet manueel - maar via een functie zoals "voorwaardelijke opmaak", maar dat lukt - mij - natuurlijk niet.
Wie weet raad??

A           B     C
recordn°     foton°    famnaam
1  HH_0001 Vermeulen
2  HH_0002        Schrooten 
3               HH_0023        Meurs  enz

A
fotoN°
HH_0001
HH_0010
HH_0023

Ik zou dus graag indien ik een foto heb met de naam HH_0001 deze cel een achtergrond kleur geven. Dit om uit te zoeken welke foto's nog extra moet gemaakt worden!
Dus B1 en B3 zouden in dit geval een achtergrond kleur moeten krijgen.

alvast bedankt,  Paco

[hovel]

Bij voorwaardelijke opmaak kan men niet verwijzen buiten het eigen tabblad.

Maar het kan met een tussenstap:
Voeg een kolom X toe aan uw gegevens bestand,
Vul deze kolom X met de formule vergelijken. bvb: =VERGELIJKEN(B2;Blad2!A:A;0)) 
Maak een voorwaardelijke opmaak voor kolom C op basis van de inhoud van kolom X. bvb: =ISGETAL(X1)

Bijkomend voordeel is dat je door het sorteren van het gegevens bestand of door het toepassen van een filter alle records waarvoor er nog geen foto is bij mekaar kunt zetten.

[Sony]

paco,

Selecteer het/een veld waarin de aan te passen cellen voorkomen.



Klik op Voorwaardelijke opmaak --> Markeringsregels voor cellen -->
Tekst met...



Vul de tekst in waarop moet gereageerd worden
Als je een keuze kunt maken uit de voorgestelde
mogelijkheden kunt je dat aanduiden anders klik
op Aangepaste Indeling



Je kunt dan een kleur aanduiden en eventueel op Opvul effecten



Ik opteerde voor groen en de eerder met rood gevulde cellen
staan dadelijk met een groene achtergrond



Als je nu in een van de cellen uit het gekozen veld vult met HH_0001
wordt de achtergrond automatisch groen.



Je kunt die instelling altijd langs dezelfde weg wijzigen of verwijderen.

Sony

[paco]

beste Novel en Sony, alvast bedankt voor de reacties!

Sony:
Hoe voorwaardelijke opmaakt werk wist ik al, maar ik kan het niet uitwerken zodat mijn data bestand van /- 8000 records automatisch die kleur krijgen zoals jij het voorstelt. Het is geen optie elk fotonummer apart in te geven.

Hovel:
indien ik de functie vergelijken gebruik krijg ik als uitkomst de relatieve positie (= record nummer) van het betroffen fotonummer. Maar dan ben ik er volgens mij nog niet. Zou dan eventueel die relatieve positie nog aan de cel moeten kunnen verbinden waarin de gezochte gegevens staan. Of zie ik dat verkeerd??

zie bijgevoegde print.
in kolom w het ik de naam van het fotonr's staan zoals die in mijn HD schijf staan.
in de kolom met hoofding "kolom2" heb ik de formule vergelijken ingezet. De uitkomst "1" is de relatieve positie van het fotonr uit de kolom w t.o.v. het gezochte fotonr dat in kolom b staat.

Om het juiste record nummer te vinden moet ik die uitkomst met de waarde 1 verhogen gezien ik in mijn tabel begin te zoeken vanaf record 2. 

Spijtig genoeg kan ik - volgens mij -  hier niet zoeken op 2 sleutels want dan had ik het probleem opgelost.

[hovel]

Hovel:
indien ik de functie vergelijken gebruik krijg ik als uitkomst de relatieve positie (= record nummer) van het betroffen fotonummer. Maar dan ben ik er volgens mij nog niet. Zou dan eventueel die relatieve positie nog aan de cel moeten kunnen verbinden waarin de gezochte gegevens staan. Of zie ik dat verkeerd??

Ik zie beide lijsten in het zelfde werkblad staan. Dat maakt het eenvoudiger. Maak volgende voorwaardelijke opmaak voor kolom B.



Resultaat:

[Sony]

paco,

met een kleine macro Ctrl + o krijg ik volgend resultaat:



De macro:
Public Sub Zoekop()
Dim woord1
Dim woord2
For i = 2 To 5
For j = 2 To 17
woord1 = "B" + Trim(Str(j))
woord2 = "M" + Trim(Str(i))
If Range(woord1) = Range(woord2) Then
Range(woord1).Interior.Color = 5296274
End If
Next j
Next i
End Sub

Sony

[Sony]

Uitgevoerd met licht aangepaste macro
als de gegevens van kolom B worden gewijzigd in data die niet
voorkomen in kolom M verdwijnt de achtergrond kleur bij het
toepassen van de macro.



Sony

[paco]

Hovel:
Bedankt!! heb toch wat moeten puzzelen, maar is uiteindelijk gelukt. Heb het nog niet uitgetest maar in de formule staat 'huidige selectie'. dit is geen beperking voor de toekomst gezien het databestand steeds zal aangroeien en we dan data buiten de 'huidige selectie' zullen hebben?


Sony:
jouw macro interesseert mij ook! Maar ik krijg een foutmelding. zie bijgevoegde bijlage! Zou je me die bijgewerkte versie waarover je ook nog sprak ook eens kunnen doorsturen? Het zou inderdaad zo moeten zijn dat indien er foto's verdwijnen of bijkomen die velden dan met of zonder achterkleur getoond worden.

krijg een 'End of Sub' foutmelding hoewel ik niet zie waar die fout zit. Heb wel de range verhoogd tot 8000 gezien er gestadig records bijkomen en om wat 'reserve' te hebben.

Alvast bedankt beide voor jullie meedenken en vooral inzet............  

groetjes,

[hovel]

Hovel:
Bedankt!! heb toch wat moeten puzzelen, maar is uiteindelijk gelukt. Heb het nog niet uitgetest maar in de formule staat 'huidige selectie'. dit is geen beperking voor de toekomst gezien het databestand steeds zal aangroeien en we dan data buiten de 'huidige selectie' zullen hebben?

De ´huidige selectie´ in de schermafdruk verwijst naar het gebied waarop de regel (=formule) van toepassing is. Ik had de volledige kolom geselecteerd dus in dit geval ´huidige selectie´ = volledige kolom.

Aangroei is dus geen probleem.

Dat zie je ook in de schermafdruk,
in vak "Regel(....) " staat: "=VERGELIJKEN(B1;W:W;0)". Dat betekend volledige kolom W.
in vak "Van toepassing op" staat "=$B:$B". Dat betekend de volledige kolom B.

[Sony]

paco,

logisch je hebt de volledige tekst overgenomen,
de regel Public Sub Zoekop() moet je echter verwijderen.
zoals het er nu staat heb je 2 sub in een macro dat gaat niet.
Voor het aanpassen van de data in kolom B is er nog een
uitbreiding nodig en wordt het de onderstaande macro
(deze macro is alleen gemaakt als voorbeeld met een
beperkt aantal data in beide kolommen).
Als er ook in andere kolommen moet gezocht worden moet er nog een
aanpassing gebeuren.
Public Sub Zoekop()
Dim Teller1 As Integer
Dim Teller2 As Integer
Dim woord1
Dim woord2
For j = 2 To 17
woord1 = "B" + Trim(Str(j))
Range(woord1).Interior.Pattern = xlNone
Next j
Teller1 = 4
Teller2 = 16
For i = 2 To 5
For j = 2 To 17
woord1 = "B" + Trim(Str(j))
woord2 = "M" + Trim(Str(i))
If Range(woord1) = Range(woord2) Then
Range(woord1).Interior.Color = 5296274
End If
Next j
Next i
End Sub

De werkwijze van hovel is zeker zou goed als de macro.

Sony

[paco]

Mag ik jullie beide nogmaals bedanken!!

jullie waren een grote hulp!! 

salukes,

Paco

[Sony]

paco,

bedankt voor het berichtje en graag gedaan.
Als er een uitbreiding moet gemaakt worden;
bvb verschillende kleuren voor verschillende aantallen
en toch ook, zoeken in andere tabbladen, laat dit dan
maar weten dan kan er een en ander aangepast worden.

Sony