Excel voorwaardelijke opmaak met formule doortrekken

[Telin]

Voor mijn werk ben ik nu bezig met een aardig grote excel sheet.
Nu wil ik de kleur van een cel aan de hand van de waarde in die cel laten aanpassen. Hiervoor gebruik ik voorwaardelijke opmaak met formule.

In dit voorbeeld heb ik de regel zo opgesteld dat cel A3 van kleur verandert naar aanleiding van het getal in die cel in verhouding tot de waardes die ik heb aangeven;

waarde cel C3 is minimum
waarde cel D3 is middelpunt
waarde cel E3 is maximum

In de uiteindelijke sheet zullen de minimum, middelpunt en maximum waardes per regel verschillen en daarom denk ik dat het makkelijkst is deze in aparte cellen te definiëren.





Deze opzet werkt een geeft het gewenste resultaat/gedrag voor cel A3.

Mijn vraag is hoe kan ik nu makkelijk/snel deze opmaak doortrekken naar cellen A4 t/m A7 zodat cel A4 de min/middel/max waardes gebruikt gedefinieerd in C4, D4 en E4. Cel A5 de min/middel/max waardes gebruikt gedefinieerd in cel C5, D5 en E5 enz. enz.

Als ik cel A3 doortrek naar beneden krijgen cel A4 t/m A7 wel de opmaak maar gebruiken ze de waardes gedefinieerd in C3, D3 en E3.

Lijkt mij dat er toch een manier moet zijn om dit snel door te trekken/kopiëren naar de cellen A4 t/m A7 terwijl de gedefinieerde cellen ook meelopen naar C4, C5, C6 enz. enz.

Als het nu om een paar regels gaat is het nog wel te doen om handmatig voor iedere cel/regel handmatig de voorwaardelijke opmaak in te voeren maar uiteindelijk is het de bedoeling dat dit toegepast wordt in een sheet van een aantal honderd regels.

Of zit ik nu op een hele verkeerde weg?
Ik ben absoluut geen Excel guru dus wellicht is er een andere/betere manier op tot hetzelfde resultaat te komen?

...

Ik maak gebruik van Microsoft Windows 10 Enterprise 21H1 x64 en Microsoft Excel Versie 2111 Build 16.0.14701.20206 x64
...

Uiteraard heb ik zitten googlen en wellicht dat ik niet de juiste zoektermen gebruik voor wat ik wil maar ik kan er niet heel veel over terug vinden. Op de Microsoft support forums heb ik een topic gevonden die denk ik over dezelfde vraag gaat en het antwoord daar was dat de persoon maar contact moet opnemen met tech support

Op een Belgisch forum heb ik ook denk ik iets vergelijkbaars gevonden en daar wordt als oplossing gebracht dat je de cellen moet "doorvoeren" uit dat topic wordt mij niet duidelijk hoe wat wat je dan zou moeten doorvoeren dus ik heb zelf wat zitten spelen met de optie zonder enig resultaat.
...

Hopelijk kan een van jullie mij helpen of in de juiste richting wijzen.

[Frappuccino]

Heb je het al geprobeerd zonder $ voor het getal? Dus $C3 ipv $C$3, $D3 ipv $D$3 en $E3 ipv $E$3.

[Telin]

Frappuccino schreef op zaterdag 11 december 2021 @ 17:25:
Heb je het al geprobeerd zonder $ voor het getal? Dus $C3 ipv $C$3, $D3 ipv $D$3 en $E3 ipv $E$3.

Dit mag niet van excel:

[Tazzios]

Bij het scherm voor de screenshot die je nu hebt ('Regels voor voorwaardeliejk opmaak beheren') heb je de kolom 'van toepassing op'

[g0tanks]

Om te bereiken wat je wil moet je VBA gebruiken die elke regel afgaat en stuk voor stuk de voorwaardelijke opmaak instelt. Ik heb hieronder een voorbeeldje voor je gemaakt met hulp van Stackoverflow. Je mag zelf even uitvogelen hoe je de code draait en aanpast naar jouw situatie.

code:

Option Explicit
Sub ApplyConditionalFormatting()
    Dim ws As Worksheet
    Set ws = ActiveWorkbook.Sheets("Sheet1") ' change to your sheet here
    Dim rw As Long
    Dim rng As Range

    For rw = 2 To 6 ' change to your respective rows
        With ws
            Set rng = .Range(.Cells(rw, "A"), .Cells(rw, "A")) ' change to your respective columns

            With rng
                .FormatConditions.AddColorScale ColorScaleType:=3
                .FormatConditions(.FormatConditions.Count).SetFirstPriority
            End With

            With rng.FormatConditions(1)
                With .ColorScaleCriteria(1)
                    .Type = xlConditionValueNumber
                    .Value = "='" & ws.Name & "'!$B$" & rw
                    .FormatColor.Color = 7039480
                End With

                With .ColorScaleCriteria(2)
                    .Type = xlConditionValueFormula
                    .Value = "='" & ws.Name & "'!$C$" & rw
                    .FormatColor.Color = 8711167
                End With

                With .ColorScaleCriteria(3)
                    .Type = xlConditionValueFormula
                    .Value = "='" & ws.Name & "'!$D$" & rw
                    .FormatColor.Color = 8109667
                End With
            End With
        End With
    Next rw
End Sub

[dix-neuf]

Telin,
Als je het jezelf niet al te moeilijk wil maken zou je 4 regels vw. opmaak kunnen instellen (met 4 kleuren), waarmee je redelijk in de buurt komt van je voorbeeld hierboven. Die regels moet je slechts 1 keer instellen voor alle cellen in kolom A. Doe het volgende:
1. Selecteer A3:A7 (of tot zover als in kolom A de regels moeten gelden) en laat die cellen geselecteerd staan tot je alle 4 regels hebt ingevoerd.
2. Duid bij vw. opmaak bij elke regel aan dat je een formule wil gebruiken om te bepalen welke cellen moeten worden opgemaakt.
3. Stel achtereenvolgens de volgende regels in:
=A3<GEMIDDELDE(C3;D3) --> rood.
=EN(A3>=GEMIDDELDE(C3;D3);A3<D3) --> oranje.
=EN(A3>=D3;A3<=GEMIDDELDE(D3;E3)) --> geel
=EN(A3>=GEMIDDELDE(D3;E3);A3<=E3) --> groen.
Hieronder een voorbeeld wat je dan als resultaat kunt krijgen.

[Telin]

g0tanks schreef op zaterdag 11 december 2021 @ 18:45:
Om te bereiken wat je wil moet je VBA gebruiken die elke regel afgaat en stuk voor stuk de voorwaardelijke opmaak instelt. Ik heb hieronder een voorbeeldje voor je gemaakt met hulp van Stackoverflow. Je mag zelf even uitvogelen hoe je de code draait en aanpast naar jouw situatie.

[Afbeelding]

code:

Option Explicit
Sub ApplyConditionalFormatting()
    Dim ws As Worksheet
    Set ws = ActiveWorkbook.Sheets("Sheet1") ' change to your sheet here
    Dim rw As Long
    Dim rng As Range

    For rw = 2 To 6 ' change to your respective rows
        With ws
            Set rng = .Range(.Cells(rw, "A"), .Cells(rw, "A")) ' change to your respective columns

            With rng
                .FormatConditions.AddColorScale ColorScaleType:=3
                .FormatConditions(.FormatConditions.Count).SetFirstPriority
            End With

            With rng.FormatConditions(1)
                With .ColorScaleCriteria(1)
                    .Type = xlConditionValueNumber
                    .Value = "='" & ws.Name & "'!$B$" & rw
                    .FormatColor.Color = 7039480
                End With

                With .ColorScaleCriteria(2)
                    .Type = xlConditionValueFormula
                    .Value = "='" & ws.Name & "'!$C$" & rw
                    .FormatColor.Color = 8711167
                End With

                With .ColorScaleCriteria(3)
                    .Type = xlConditionValueFormula
                    .Value = "='" & ws.Name & "'!$D$" & rw
                    .FormatColor.Color = 8109667
                End With
            End With
        End With
    Next rw
End Sub

Dank voor het antwoord.
Ik heb even zitten prutsen en ik heb het nu de VBA werkend
Maar... om Blad1 wat netter en overzichtelijker te houden/maken heb ik het idee om de waardes in Blad2 in te vullen.

Hoe kan ik de VBA aanpassen zodat de waarden in kolom C, D en E van Blad2 gebruikt worden?
Ik dacht dit makkelijk te doen door Blad2 aan de kolom referentie toe te voegen maar dan krijg ik een foutmelding.. Ik heb zitten neuzen op Stackoverflow maar ik kan daar nog niet echt vinden wat ik zoek/bedoel.. ik zal ongetwijfeld wel naar de verkeerde termen zoeken.

[dix-neuf]

Telin schreef op zondag 12 december 2021 @ 16:05:
Hoe kan ik de VBA aanpassen zodat de waarden in kolom C, D en E van Blad2 gebruikt worden?

Als Blad1 de naam is van het eerste werkblad, wijzig dan in regel 4 "Sheet1" in "Blad1" .
Als Blad2 de naam is van het tweede werkblad, wijzig dan:
.Value = "='" & ws.Name & "'!$B$" & rw in: .Value = "=Blad2!$B$" & rw
.Value = "='" & ws.Name & "'!$C$" & rw in: .Value = "=Blad2!$C$" & rw
.Value = "='" & ws.Name & "'!$D$" & rw in: .Value = "=Blad2!$D$" & rw

[Telin]

dix-neuf schreef op zondag 12 december 2021 @ 20:05:
[...]

Als Blad1 de naam is van het eerste werkblad, wijzig dan in regel 4 "Sheet1" in "Blad1" .
Als Blad2 de naam is van het tweede werkblad, wijzig dan:
.Value = "='" & ws.Name & "'!$B$" & rw in: .Value = "=Blad2!$B$" & rw
.Value = "='" & ws.Name & "'!$C$" & rw in: .Value = "=Blad2!$C$" & rw
.Value = "='" & ws.Name & "'!$D$" & rw in: .Value = "=Blad2!$D$" & rw

Heel erg bedankt!

Ik heb de macro aangepast en succesvol geïmplementeerd is mijn daadwerkelijke datasheet, alles werkt zoals gehoopt/gewenst!.

ps.
In jou macro staat bij eerste conditie ValueNumber en bij de tweede en derde conditie ValueFormula
Ik heb het aangepast dat alle drie de condities ValueNumber heeft.
Zover ik kan zien werk het goed op deze mannier, ben niet zeker wat ValueFormula kan/zou veranderen daar de cel een waarde heeft die niet opgemaakt is met een formule.

[ Voor 3% gewijzigd door Telin op 13-12-2021 12:27 ]

[dix-neuf]

Het gebruik van zowel ValueNumber als ValueFormula in de macro die g0tanks plaatste was me nog niet opgevallen; ik heb uitsluitend gekeken naar de bladnamen, omdat daar je vraag over ging.
Omdat het resultaat van .value uitsluitend een getal kan zijn, zou ik xlConditionValueNumber gebruiken.

[Sethro]

Snap überhaupt niet waarom hier met een macro word gewerkt.

Als je een groter bereik selecteert dan kun je de conditional format meteen voor een groot aantal cellen creëren.

[g0tanks]

Sethro schreef op maandag 13 december 2021 @ 16:50:
Snap überhaupt niet waarom hier met een macro word gewerkt.

Als je een groter bereik selecteert dan kun je de conditional format meteen voor een groot aantal cellen creëren.

Het probleem is dat de voorwaardelijke opmaak (minimum, middelpunt, maximum) per regel anders moet zijn. Dat lukt niet door in één keer een groter bereik te selecteren.

[Sethro]

Jawel want die waardes staan gewoon in de cellen? Als je een groter bereik selecteert dan stel je deze in voor de bovenste rij en Excel past dat dan slim toe op alle rijen. Wel de optie pakken om met formule te werken.

[ Voor 13% gewijzigd door Sethro op 13-12-2021 17:13 ]

[g0tanks]

Sethro schreef op maandag 13 december 2021 @ 17:12:
Jawel want die waardes staan gewoon in de cellen? Als je een groter bereik selecteert dan stel je deze in voor de bovenste rij en Excel past dat dan slim toe op alle rijen. Wel de optie pakken om met formule te werken.

Mij is het niet gelukt. Voorzover ik weet kan je bij werken met celwaarden in voorwaardelijke opmaak geen gebruik maken van relatieve referenties.

[Sethro]

In de formule optie werkt dat wel

Random voorbeeld:

https://www.ablebits.com/...onal-formatting-formulas/

[ Voor 64% gewijzigd door Sethro op 13-12-2021 18:06 ]

[dix-neuf]

Sethro schreef op maandag 13 december 2021 @ 17:58:
In de formule optie werkt dat wel.

Dat vermeldde ik al en daarvan gaf ik ook een voorbeeld in mijn bericht van 12 december 2021 00:14 uur hierboven.

[Telin]

Ik heb niet echt de Excel kennis om mijn eigen te mengen in de discussie welke weg wel al dan niet beter is en waarom. Ik ben tevreden dat via deze weg in ieder geval mijn sheet werkt zoals ik voor ogen had.