Cellen met bepaalde kleur optellen

[sherpa14]

ik wil in een werkblad de waarden van cellen optellen die in eenzelfde celkleur staan.
graag wil ik hiervoor de kleuren
geel,
oranje,
hemelsblauw,
olijfgroen accent3,
licht oranje-achtergrond 2-donkerder 25% gebruiken

van visual basis heb ik totaal geen kaas gegeten, dus als het via deze weg moet: dan graag stap voor stap

 

[plongske]

Sherpa,
welke waarden stellen die kleuren voor?
Maak eens een voorbeeldbestand.

 

[Haije]

sherpa,

met welke Excel-versie werk je?

 

[sherpa14]

de weerden stellen uren voor
Ik werk met excel 2010

in bijlage een voorbeeld

 

[arrie23]

Met dank aan iemand van dit forum een UDF (ik weet helaas niet meer wie maar ere wie ere toekomt).

Ga in Excel 2010 naar Ontwikkelaars-Visual Basic (eventueel eerst via Bestand-Opties-Lint Aanpassen Ontwikkelaars aanvinken)
Klik in het schermpje Project-VBA Project op rechtermuisknop en kies: invoegen - Module
Plak onderstaande code in het lege scherm:

Function SOMCELKLEUR(r As Range, KleurCel As Range) As Double
    Application.Volatile
    For Each Cel In r.Cells
        If Cel.Interior.ColorIndex = KleurCel.Interior.ColorIndex And IsNumeric(Cel.Value) Then
            SOMCELKLEUR = SOMCELKLEUR + Cel.Value
        End If
    Next Cel
End Function

Ga weer terug naar je bestand. Er is nu een, door gebruiker gedefinieerde, nieuwe formule: =SOMCELKLEUR
Ga in de cel staan waar je de sommatie wil doen. Kies voor functie invoegen (fx) en kies bij categorie voor 'Door gebruiker gedefinieerd' en kies voor SOMCELKLEUR.
Kies bij R voor het bereik/de range waarover je wilt optellen en bij KleurCel klik je een cel aan die de gewenste kleur heeft waarover je wilt optellen. Het resultaat is de som van alle cellen binnen het opgegeven bereik R die de kleur hebben van KleurCel.
Charmante van deze Functie is dat je de kleurcode van de betreffende kleur niet hoeft te kennen.

Wellicht ten overvloede: vergeet niet om dit bestand op te slaan als .xlsm ipv .xlsx anders ben je de formule de volgende keer weer kwijt

 

[sherpa14]

ik krijg bij de keuze van de functie de "somcelkleur" nergens te zien. Moet die vanuit VB dan eerst ergens worden opgeslagen?
(zoal ik al aangaf: ik heb echt geen kaas gegeven mbt VB) Ik kan de categorie 'door gebruiker gedefinieerd' niet terugvinden.

 

[arrie23]

Ik heb het voor je toegevoegd aan je bestand, hij zou er nu wel tussen moeten staan (bij eventuele waarschuwing wel kiezen voor macro's inschakelen).
In principe hoef je het niet eerst op te slaan, als je de code in het modulevenster hebt staan kun je via het Excel-icoon terug naar je bestand en kun je de formule direct gebruiken. Daarna je bestand dus wel opslaan als .xlsm en niet als .xlsx

 

[sherpa14]

Prachtig de sommen worden netjes gemaakt. De som echter heeft geen kleurtje meegekregen.

nog 2 vragen hierover:
1. hoe komt het dat bij mij de categorie "door de gebruiker" niet te vinden was - en daardoor ook de naam van de functie ook niet te vinden was
2. hoe kan ik er nu ook voor zogen dat het resultaat van die sommen ook in dezelfde kleur komt zoals de kleur van de afzonderlijke waarden die werden opgeteld.

 

[arrie23]

1. Dat durf ik niet te zeggen, wellicht is er toch iets niet helemaal goed gegaan bij het plaatsen van de code in het Modulevenster oid.
2. Je zou ze natuurlijk gewoon handmatig dezelfde kleur kunnen geven (bijv. met het kwastje opmaak kopieren/plakken). Je kan, als je de resultaatcellen handmatig kleurt, in dat geval ook KleurCel uit de formule verwijzen naar de cel waar het resultaat van de sommatie in komt te staan. Zodra je dan de kleur van de resultaatcel wijzigt krijg je automatisch nieuw resultaat.

Wellicht is het ook aan te passen in de UDF-code maar, zoals gezegd, heb ik deze van dit forum geplukt. Ik kan hem wel toepassen maar aanpassen is een ander verhaal. Wellicht iemand anders?

 

[sherpa14]

Ik heb het uiteindelijk opgelost met voorwaardelijke opmaak indien de celwaarde meer dan 0 is. Natuurlijk al de initiële celkleur wijzigt dan moet de voorwaardelijke opmaak-kleur ook handmatig worden aangepast.
Het automatisch aanpassen van de kleur lukt me niet. ik had nochtans een formule gebruikt - =$AM$4>0;celkleur:AK2 - maar dat lukt niet, en dat heeft in de eerste plaats te maken met het gebruik van haakjes. (AK2 is de cel met het gewenste kleur) ik ben daar jammer genoeg niet zo sterk ik