Excel - Roosterplanning kleuren (bij x invoer)

[Zaibatsu]

Hoi Tweakers,

Momenteel ben ik een planning aan het maken met Excel (eigenlijk verbeteren). Nou ben ik redelijkwijs handig met VBA maar hier kom ik echt niet mee uit:

Ik wil een planning maken waarin ik in iedere E cel een naam kan plaatsen. De namen kunnen meerdere keren voorkomen en staan niet vast gepositioneerd (het zal dus nooit zo zijn dat Peter altijd op E14 zal staan). Met 'Voorwaardelijke opmaak' heb ik de namen een kleur gegeven, namelijk:
- Peter = Rood
- Jan = Geel
- Piet = Groen

Wanneer er bij cel E14 'Peter' staat (en de cel dus rood kleurt) wil ik in cel F14, G14 en H14 een X plaatsen zodat deze dezelfde kleur krijgt als cel E14 (ofwel rood). Maar wanneer ik de naam 'Peter' verander in 'Jan' dan moeten cel F14, G14 en H14 ook geel worden.

Waarom X? Omdat V grijs wordt (vakantie) en S ook grijs wordt (scholing).

Let wel op. Ik heb celverwijzingen gebruikt. Dat houdt in dat wanneer Peter uit dienst treedt en er een Dennis komt dat Dennis de kleur Rood zal dragen.

Ik hoop zo duidelijk te zijn geweest. Indien dit niet het geval is hoor ik dat graag van jullie. Echter een goeie duw in de rug zal mij (denk ik) ook helpen om zelf naar de oplossing te zoeken
(Leren is altijd leuker dan overschrijven).


Groetjes,
Chris

[Orion84]

Je kan die voorwaardelijke opmaak van de overige cellen toch gewoon (mede) laten afhangen van wat er in Kolom E staat?

[Zaibatsu]

Orion84 schreef op maandag 30 april 2018 @ 16:40:
Je kan die voorwaardelijke opmaak van de overige cellen toch gewoon (mede) laten afhangen van wat er in Kolom E staat?

Euhm, ik kan dan enkel bij X een kleur aangeven. Dus dan is de kleur niet afhankelijk van de naam die in E staat of ga ik de verkeerde richting op?

PS. Ik werk met Excel 2010 :x

[Orion84]

Je kan toch gewoon de opmaak afhankelijk laten zijn van een formule en die formule zowel op Cel=X en op Cel in kolom E is Pietje laten controleren?

[Lustucru]

Dat of handig kiezen dat voorwaardelijke opmaak stopt bij een specifieke conditie, maw
V ->grijs
S->grijs
blanco->wit en stop
Peter->Rood
Jan->Geel

[breew]

Omdat er om een vba-richting wordt gevraagd (en omdat het kan ), een proof-of-conceptje..

Visual Basic:

Option Explicit

Sub KleurenMaar()

  Dim c As Range
  Dim rngNamen As Range
  Dim rngNamenBron As Range
  Dim arrKleuren() As Variant
  Dim arrNamen() As Variant
  Dim i As Long
  Dim blnGevonden As Boolean
  
  'initialisatie
  i = 0
  With ActiveSheet
    'bronnamen + kleuren
    Set rngNamenBron = .Range("A1:A4")
    'rij met namen
    Set rngNamen = .Range("C1:C10")
    'vullen van de arrays met namen en kleuren
    'arrays opnieuw declareren
    ReDim arrKleuren(0 To rngNamenBron.Count)
    ReDim arrNamen(0 To rngNamenBron.Count)
    'arrays vullen
    For Each c In rngNamenBron
      'naam uit de cel halen en in array stoppen
      arrNamen(i) = c.Value
      arrKleuren(i) = c.Interior.Color
      'lusteller ophogen
      i = i + 1
    Next c
  End With

  'namen uit rngNamen een kleurtje geven
  For Each c In rngNamen
    'boolean instellen op false
    blnGevonden = False
    'kijk of de naam gevonden wordt in de array met namen
    For i = 0 To rngNamenBron.Count
      If c.Value = arrNamen(i) Then
        'ja, dan kleur aan cel geven
        c.Interior.Color = arrKleuren(i)
        blnGevonden = True
      End If
      'komt de kleur niet voor in de array
      If blnGevonden = False Then
        'celkleur verwijderen
        c.Interior.Color = xlNone
      End If
    Next i
  Next c

End Sub

[ Voor 3% gewijzigd door breew op 30-04-2018 18:21 . Reden: colorindex vervangen met color ]

[Zaibatsu]

Goeie morgen allen,

Ik denk dat ik niet helemaal duidelijk ben geweest en zal alles nog eens even toelichten.
In sheet 2 heb ik de benaming geplaatst (Jan, Kees, Piet, enzovoort). Al deze namen hebben een kleur (zie reactie breew). Dit heb ik ook gedaan met voorwaardelijke opmaak.
In sheet 1 wordt dus Piet weergeven in een groene cel met zwarte letters (voorwaardelijke opmaak). Maar wat ik nu wil is dat alle opvolgende cellen (horizontaal) met slechts 1 toets (zoals de toets X) dezelfde kleur krijgen (ik heb een voorbeeldje:


Let wel op, de positie van Piet en Klaas kunnen bijvoorbeeld een keertje wisselen (en de kleuren dus ook).

@breew Thnx voor je reactie, hier wil ik namelijk ook iets mee gaan doen (i.p.v. voorwaardelijke opmaak). Hier ga ik deze week nog mee stoeien

Alvast bedankt allen!

[Orion84]

Heb je Orion84 in "Excel - Roosterplanning kleuren (bij x invoer)" inmiddels al geprobeerd? Want ik begreep prima waar je naar op zoek was

[Zaibatsu]

Hoi @Orion84 ,

Ik had even over jouw reactie heen gelezen (sorry ). Ik kan wel de volgende formule toepassen:
=EN($E8=DATA!$G$9;F8="x") en deze dan weer door kopiëren naar alle andere cellen (maar dat kost heel veel tijd). Tevens komt erbij dat ik deze formule toe mag passen (en kleur) voor circa 30 namen.

Ik dacht dat het met VBA wat makkelijker zou gaan (ook zodat je de planning oneindig groot kan maken). :x

Wel super bedankt voor de tip want mocht ik er niet mee uitkomen met VBA dan pas ik jouw idee toe.

[Orion84]

Zaibatsu schreef op dinsdag 1 mei 2018 @ 13:48:
Hoi @Orion84 ,

Ik had even over jouw reactie heen gelezen (sorry ). Ik kan wel de volgende formule toepassen:
=EN($E8=DATA!$G$9;F8="x") en deze dan weer door kopiëren naar alle andere cellen (maar dat kost heel veel tijd).

Hoezo kost dat heel veel tijd? Kwestie van de regel aanmaken en toepassen op het hele planningsbereik toch?

Tevens komt erbij dat ik deze formule toe mag passen (en kleur) voor circa 30 namen.

Daar ga je sowieso niet aan ontkomen natuurlijk.

Ik dacht dat het met VBA wat makkelijker zou gaan (ook zodat je de planning oneindig groot kan maken). :x

Wel super bedankt voor de tip want mocht ik er niet mee uitkomen met VBA dan pas ik jouw idee toe.

[breew]

Zaibatsu schreef op dinsdag 1 mei 2018 @ 11:27:
[afbeelding]
Let wel op, de positie van Piet en Klaas kunnen bijvoorbeeld een keertje wisselen (en de kleuren dus ook).

omdat het kan..

input:


output:

Visual Basic:

Option Explicit

Sub KleurenMaar2()

  Dim c As Range, c2 As Range, rngNamen As Range, rngX As Range
  Dim lonLaatsteRij As Long, lonLaatsteKolom As Long
  
  'initialisatie
  With ActiveSheet
    'vind de onderste rij met data ik kolom A
    lonLaatsteRij = .Cells(Rows.Count, "A").End(xlUp).Row
    'stel bereik met namen in
    Set rngNamen = .Range(.Cells(1, 1), .Cells(lonLaatsteRij, 1))
  End With
  
  'loop door de cellen in bereik rngNamen
  For Each c In rngNamen
    'bepaal voor elke cel in rngNamen
    With ActiveSheet
      'de meest rechter kolom met een data
      lonLaatsteKolom = .Cells(2, Columns.Count).End(xlToLeft).Column
      'het bereik met de x-en
      Set rngX = .Range(.Cells(c.Row, 2), .Cells(c.Row, lonLaatsteKolom))
    End With
    'loop door alle cellen in het bereik met x-en
    For Each c2 In rngX
      'staat er een x in de cel?
      If c2.Value = "x" Then
        'neem achtergrondkleur en fontkleur van de cel in kolom A over
        c2.Interior.Color = c.Interior.Color
        c2.Font.Color = c.Font.Color
      Else
        'geen x? dan geen achtergrondkleur en automatische fontkleur
        c2.Interior.Color = xlNone
        c2.Font.Color = xlAutomatic
      End If
    Next c2
  Next c
  
End Sub

[vegetoot]

Zaibatsu schreef op dinsdag 1 mei 2018 @ 11:27:
Goeie morgen allen,

Ik denk dat ik niet helemaal duidelijk ben geweest en zal alles nog eens even toelichten.
In sheet 2 heb ik de benaming geplaatst (Jan, Kees, Piet, enzovoort). Al deze namen hebben een kleur (zie reactie breew). Dit heb ik ook gedaan met voorwaardelijke opmaak.
In sheet 1 wordt dus Piet weergeven in een groene cel met zwarte letters (voorwaardelijke opmaak). Maar wat ik nu wil is dat alle opvolgende cellen (horizontaal) met slechts 1 toets (zoals de toets X) dezelfde kleur krijgen (ik heb een voorbeeldje:
[afbeelding]

Let wel op, de positie van Piet en Klaas kunnen bijvoorbeeld een keertje wisselen (en de kleuren dus ook).

@breew Thnx voor je reactie, hier wil ik namelijk ook iets mee gaan doen (i.p.v. voorwaardelijke opmaak). Hier ga ik deze week nog mee stoeien

Alvast bedankt allen!

Volgens mij moet je het omdraaien: kleur de hele rij en geef dan voorwaarlijke opmaak op de hele rij: met de standaardregels van Excel conditional formatting, waarin je aangeeft dat lege cellen wit gevuld zijn.

[Zaibatsu]

omdat het kan..

input:
[afbeelding]

output:
[afbeelding]

Visual Basic:

Option Explicit

Sub KleurenMaar2()

  Dim c As Range, c2 As Range, rngNamen As Range, rngX As Range
  Dim lonLaatsteRij As Long, lonLaatsteKolom As Long
  
  'initialisatie
  With ActiveSheet
    'vind de onderste rij met data ik kolom A
    lonLaatsteRij = .Cells(Rows.Count, "A").End(xlUp).Row
    'stel bereik met namen in
    Set rngNamen = .Range(.Cells(1, 1), .Cells(lonLaatsteRij, 1))
  End With
  
  'loop door de cellen in bereik rngNamen
  For Each c In rngNamen
    'bepaal voor elke cel in rngNamen
    With ActiveSheet
      'de meest rechter kolom met een data
      lonLaatsteKolom = .Cells(2, Columns.Count).End(xlToLeft).Column
      'het bereik met de x-en
      Set rngX = .Range(.Cells(c.Row, 2), .Cells(c.Row, lonLaatsteKolom))
    End With
    'loop door alle cellen in het bereik met x-en
    For Each c2 In rngX
      'staat er een x in de cel?
      If c2.Value = "x" Then
        'neem achtergrondkleur en fontkleur van de cel in kolom A over
        c2.Interior.Color = c.Interior.Color
        c2.Font.Color = c.Font.Color
      Else
        'geen x? dan geen achtergrondkleur en automatische fontkleur
        c2.Interior.Color = xlNone
        c2.Font.Color = xlAutomatic
      End If
    Next c2
  Next c
  
End Sub

[/quote]

Helaas, mijn teksten worden Paars en waar ik X plaats gebeurt er niets. Ik ben helaas bang dat wat jij mij probeert uit te leggen ver boven mijn pet gaat (en dus niet kan spelen met de code)

Ik heb lopen puzzelen met @Orion84 zijn idee en dat werkt het soepelst momenteel (en kan ik mooi toepassen nu).

Iedereen enorm bedankt. Ik zal kijken of ik binnenkort het bestandje online kan plaatsen (wel zo eerlijk om mijn doel te delen met jullie en andere Tweakers).

[Bolletje]

Code van @breew werkt wel, net getest; alleen zijn er sommige dingen hard coded, waaronder de beginkolom. Als je hem in je eigen workbook wilt hebben moet je dus even die even aanpassen.

[breew]

Bolletje schreef op dinsdag 1 mei 2018 @ 17:28:
Code van @breew werkt wel, net getest; alleen zijn er sommige dingen hard coded, waaronder de beginkolom. Als je hem in je eigen workbook wilt hebben moet je dus even die even aanpassen.

Het is inderdaad een proof-of-concept, dus ik heb e.e.a. beperkt tot het voorbeeld

@Zaibatsu: Met basale excel-vba-kennis (en/of google) moet je de code vrij eenvoudig om kunnen bouwen naar iets wat werkt voor jouw sheet. Er staat (imho) voldoende helder commentaar bij de code om je een stuk op weg te helpen.
Begrijp je wat de code functioneel doet? Dat is stap 1.

[Zaibatsu]

Aller eerst excuus voor de late reactie. Helaas kwam er iets tussen waardoor ik niet verder was gegaan.

@breew jouw methode werkt inderdaad maar dan moet de cel in de sheet gekleurd zijn en ingevoerd. Nu is dit niet het geval (voorwaardelijke opmaak) waardoor de kleur op een of ander manier niet opgepakt wordt. Tevens krijg ik bij cellen erboven een rare 'rode' kleur (deze blijft maar terug komen). Maar ik wil hier zeker nog op terug komen

Ik heb nu @Orion84 z'n methode toegepast en het werkt vlekkeloos.
Echter komt er weer een nieuwe eis aan te pas maar omdat dit een hele andere vraag is open ik een nieuwe Excel topic.

Nogmaals super bedankt iedereen

PS. Hier kan men het bestand downloaden: https://we.tl/zdEZu5q2XW