[Excel] cel achtergrond kleur wijzigen

[Anoniem: 98865]

Hoi iedereen,

Ik heb een Pivot tabel die ongeveer 100 x 100 groot is. Deze is voornamelijk opgebouwd uit blokken van 3 velden onder elkaar. Veld 1 bevan een vaste waarde Veld 2 bevat ene waarde dat langzaam opgehoogd word tot hij gelijk is aan Veld 1 en een Veld 3 die procentueel de voortgang van de ophoging weergeeft. Ik wil het volgende: als Veld 3 onder de 25% is moet het blok rood gekleurd worden, als Veld 3 25% tot 50% is het blok geel kleuren, als als Veld 3 50% tot 75% is het blok licht geel kleuren en als Veld 3 75% tot 100% is het blok groen kleuren.

Ik ga er vanuit dat dit doormiddel van een macro moet omdat Conditional formatting maar 3 opties heeft, waar ik voor vrees is dat ik per blok een loop moet maken dat houd in zo'n 3000 loops.

Iemand die hier een leuke/makkelijke oplossing heeft.

Alvast bedankt

[superduper]

Ik dacht ook dat in de nieuwe office conditional format maar 3 opties heeft. Ik heb zelf ook eens zoiets gemaakt, en dat moet inderdaad met een macro. Misschien zijn er wel invoegtoepassingen (is eigenlijk ook macros) die wat simpeler heat maps kunnen maken.

Ok, in Office 2007 lijkt wel iets te zitten als heat map:

http://blogs.msdn.com/excel/archive/2005/10/06/477948.aspx

Of je dat kan loslaten op je pivot table weet ik niet...

[Anoniem: 98865]

superduper schreef op maandag 27 november 2006 @ 17:15:
Ik dacht ook dat in de nieuwe office conditional format maar 3 opties heeft. Ik heb zelf ook eens zoiets gemaakt, en dat moet inderdaad met een macro. Misschien zijn er wel invoegtoepassingen (is eigenlijk ook macros) die wat simpeler heat maps kunnen maken.

Ok, in Office 2007 lijkt wel iets te zitten als heat map:

http://blogs.msdn.com/excel/archive/2005/10/06/477948.aspx

Of je dat kan loslaten op je pivot table weet ik niet...

Dit is uitstekend, het probleem is dat ik geen 2007 mag gebruiken, het meot namelijk ook naar mijn baas en hij is neit van plan ff nieuwe office te leren

[Anoniem: 113297]

3 voorwaardelijke+1 standaardopmaak=4, dus zou het in principe kunnen met voorwaardelijke opmaak en de standaard opmaak voor ofwel de <=25% ofwel de >=75%.

[Anoniem: 98865]

Anoniem: 113297 schreef op maandag 27 november 2006 @ 17:44:
3 voorwaardelijke+1 standaardopmaak=4, dus zou het in principe kunnen met voorwaardelijke opmaak en de standaard opmaak voor ofwel de <=25% ofwel de >=75%.

Nee, dat gaat niet want ik heb ook lege cellen en die moeten wit blijven en soms verandere cellen van geen waarde naar een 0% waarde afhankelijk van wat externe omstandigheden.

Het is:
0% tot 25% = rood
25% tot 50% = geel
50% tot 75% = licht groen
75% tot 100% = groen

[Anoniem: 98865]

Ik heb zelf al het volgende:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

With Target

Select Case .Value
Case "0%"
.Interior.ColorIndex = 43
Case "25%
.Interior.ColorIndex = 10
Case Else
.Interior.ColorIndex = xlNone
End Select

End With
End Sub"

Maar ik ben nog niet helemaal achter hoe ik de Min en max waardes er aan kan geven. Zoiets als >=0% <=25%

Iemand die het wel weet?

[Anoniem: 98865]

bump

[TrailBlazer]

maak je toch meerde if en elsif staments aan

code:

code
If .Value>0 and .Value<25 then
.Interior.ColorIndex = 43

ElseIf .Value>25 and .Value<50 then
.Interior.ColorIndex = 11


Else
.Interior.ColorIndex = 113
End If

[ Voor 68% gewijzigd door TrailBlazer op 30-11-2006 09:10 ]

[Lustucru]

Case Is

Maar dat staat letterlijk en uitgebreid in de help. Druk eens op F1 als je cursor op case staat.
Iig zou ik geen strings "25%" gaan vergelijken maar numerieke waarden.

[TrailBlazer]

niesje schreef op donderdag 30 november 2006 @ 09:09:
Case Is

Maar dat staat letterlijk en uitgebreid in de help. Druk eens op F1 als je cursor op case staat.
Iig zou ik geen strings "25%" gaan vergelijken maar numerieke waarden.

ik ging ervanuit dat die % er door de formatting achter zat dan kan je ze denk ik wel gewoon vergelijken met numerieke waardes.

[Bolukan]

Belangrijk is selectie van de juiste cellen (alleen de percentages), hier met behulp van numberformat. (Ook zo instellen op je werkblad)
Volgorde select case is van belang, of je moet 0.25 to 0.4999 gaan tikken en dan krijg je natuurlijk een keer 0.49991

code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim cell As Range

For Each cell In Target.Cells
 With cell
  If .NumberFormat = "0%" Then
    Select Case .Value
     Case 0.75 To 1
      .Interior.ColorIndex = 4 ' Groen
     Case 0.5 To 0.75
      .Interior.ColorIndex = 35 ' lichtgroen
     Case 0.25 To 0.5
      .Interior.ColorIndex = 40 ' Geel/Oranje
     Case 0 To 0.25
      .Interior.ColorIndex = 3 ' Rood
     Case Else
      .Interior.ColorIndex = xlNone
    End Select
   End If
  End With
 Next cell

End Sub

[Anoniem: 98865]

Heel erg bedankt voro de code werk uitstekend heb nog een laatste vraag. Als een cell helemaal leeg is kleurt de macro hem nu ook rood net als 0% is er een manier om lege cellen wit te laten?

[ Voor 78% gewijzigd door Anoniem: 98865 op 04-12-2006 11:11 ]

[Anoniem: 98865]

Allereerst heel erg bedank voor alle comentaar + oplossingen. Ik heb zelf (trial and error) mijn laatste vraga opgelost (zie heirboven).

Voor degene die nog interesse hebben in de code bij deze:

code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim cell As Range

For Each cell In Target.Cells
 With cell
  If .NumberFormat = "0%" Then
    Select Case .Value
     Case 0.75 To 1
      .Interior.ColorIndex = 4 ' Groen
     Case 0.5 To 0.75
      .Interior.ColorIndex = 6 ' lichtgeel
     Case 0.25 To 0.5
      .Interior.ColorIndex = 46 ' Geel/Oranje
     Case 0 To 0.25
      .Interior.ColorIndex = 3 ' Rood
     Case Else
      .Interior.ColorIndex = xlNone ' wit
    End Select
   End If
    If IsEmpty(.Value) Then
    .Interior.ColorIndex = xlNone
   End If
  End With
 Next cell

End Sub

[jvo]

Anoniem: 98865 schreef op dinsdag 28 november 2006 @ 12:01:
[...]


Nee, dat gaat niet want ik heb ook lege cellen en die moeten wit blijven en soms verandere cellen van geen waarde naar een 0% waarde afhankelijk van wat externe omstandigheden.

Het is:
0% tot 25% = rood
25% tot 50% = geel
50% tot 75% = licht groen
75% tot 100% = groen

Hmmm, dat gaat dan nog steeds wel. Selecteer in dat geval eerst alleen de niet lege cellen:
- Met de celaanwijzer ergens in het blok gaan staan
- Ctrl + * om het hele blok te selecteren
- F5 voor go to
- Kies in go to voor special..., (constants/formula's)

Vanaf daar gewoon 1 standaardopmaak + 3x voorwaardelijk gebruiken.

TrailBlazer schreef op maandag 04 december 2006 @ 21:41:
TS heeft al een prima macro gebouwd

Ja, dat zag ik pas later. In mijn ogen is een macro een laatste redmiddel, dus is het ter referentie misschien handig om een oplossing zonder macro in de thread te hebben.

[ Voor 18% gewijzigd door jvo op 04-12-2006 22:32 ]

[TrailBlazer]

TS heeft al een prima macro gebouwd

no shit sherlock! En dan mag je niet meer reageren van je?

[ Voor 51% gewijzigd door Lustucru op 04-12-2006 23:35 ]

[TrailBlazer]

jvo schreef op maandag 04 december 2006 @ 21:35:
[...]

Hmmm, dat gaat dan nog steeds wel. Selecteer in dat geval eerst alleen de niet lege cellen:
- Met de celaanwijzer ergens in het blok gaan staan
- Ctrl + * om het hele blok te selecteren
- F5 voor go to
- Kies in go to voor special..., (constants/formula's)

Vanaf daar gewoon 1 standaardopmaak + 3x voorwaardelijk gebruiken.

[...]

Ja, dat zag ik pas later. In mijn ogen is een macro een laatste redmiddel, dus is het ter referentie misschien handig om een oplossing zonder macro in de thread te hebben.

Een macro is volgens mij makkelijker te verplaatsen tussen verschillende documenten.

[Anoniem: 98865]

Leuk aan deze situatie is dat het allemaal variabele gegevens gaat. Het is een sheet waarin ik bijhou hoeveel data er in een tabel in de X database geladen is. Omdat het het laden dmv van 2 databases in stappen gaat waardoor 1 als een schoonmaak systeem gebruikt wordt veranderen de waardes vrij vaak weer naar nul. En voor sommige databases blijven bepaalde objecten leeg en andere weer niet dus dat betekent dat ik iedere keer er een nieuwe load cycle begint alle cond formatting opnieuw zou moeten doen, daarnaast is het zo dat er objecten inees out of scope komen of juist in scope waardoor ik voor de desbetreffende objecten weer cond formatting moet aanmaken. In mijn ogen moet je het zo gemakkelijk mogelijk voor jezelf maken (automatiseren). Daarom ben ik heel er erg blij met de macro aangezien deze mij erg veel tijd bespaart.