Mise en forme sur un tableau de variables Vba

[Calvus]

Bonsoir le Forum,

Un problème qui devient insoluble, et pourtant je ne dois pas être loin de la solution.

• J'ai un fichier avec un tableau (I7:AF57) se remplissant en fonction de la saisie d'un chiffre de 1 à 10 correspondant à l'autre tableau sur la feuille (B29: D39).

Si on inscrit 2 en K34 par exemple, Jean va s'inscrire dans ladite cellule.

J'avais un code avec For each cel....etc, mais la durée d'éxécution était de 2 minutes.
J'ai avantageusement remplacé ce code par 2 tableaux (tablo et tablo2), et fait mes boucles dessus.
Tout ça fonctionne correctement.

Ce qui "pêche", c'est que mon code précédant coloriait les cellules en fonction de la valeur obtenue, ce que je ne parviens plus à faire avec mes tableaux VBA.

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim i As Single, j As Single, k As Single, cel As Range
Dim tablo(10, 2), tablo2(51, 32)


If Not Application.Intersect(Target, Range("I7:AF57")) Is Nothing Then
'Alimente le 1er tableau des prénoms
For i = 1 To 10
tablo(i, 1) = Range("B" & i + 29)
tablo(i, 2) = Range("C" & i + 29)
Next

'Enregistrement des valeurs du tableau existant
Range("I7:AF57").Interior.ColorIndex = 2
For j = 7 To 57
For k = 9 To 32
tablo2(j - 7, k - 9) = Cells(j, k)
Next k
Next j

'Incrémentation des nouvelles valeurs
For i = 0 To 50
For j = 0 To 24
For k = 1 To 10
If tablo2(i, j) = tablo(k, 1) Then
tablo2(i, j) = tablo(k, 2)
'If tablo2(i, j) = tablo(k, 2) Then
'Cells(tablo2(i, j), tablo2(i, j)).Offset(6, 8).Interior.ColorIndex = 43
'End If
End If
Next
Next
Next
Range("I7").Resize(UBound(tablo2, 1), UBound(tablo2, 2)) = tablo2

End If 'intersect
   
End Sub

Comment faire donc pour colorer les cellules ? (Exemple de couleur sur la droite de la feuille)
Je viens de m'apercevoir en faisant l'exemple, qu'en plus mon tableau déborde.... !

• De plus, j'ai une instruction qui fonctionnait, même si c'était de manière imparfaite :

'If tablo2(i, j) = tablo(k, 2) Then
'Cells(tablo2(i, j), tablo2(i, j)).Offset(6, 8).Interior.ColorIndex = 43
'End If

En effet, les cellules se décalent, ce que j'ai compris, et pense même savoir pourquoi, mais ce que j'aimerais savoir, c'est pourquoi elle provoque un bug si elle est insérée dans le reste du code.
Vous me répondrez probablement qu'on ne peut pas affecter une couleur à une variable..

En espérant que ce soit assez clair.
Merci de votre aide.

 

[Dranreb]

Bonsoir.
Comme vous alimentez vos tableaux, vous ne gagnez rien à les utiliser.
Il faut définir des tableaux dynamiques et y charger et en décharger en une seule instruction la totalité des plages.
Pour la petite table de correspondance vous auriez intérêt à la charger d'abord intégralement dans un tableau, puis de là dans un Dictionary.
Pour la couleur vous pourriez aussi le faire d'un coup avec [I7:AF57].SpecialCells(xlCellTypeConstants, 1).ColorIndex = 43

 

[Calvus]

Bonsoir Dranreb,

Merci de votre réponse.
Vous pouvez me dire quelle piste je dois suivre ?
Je ne suis pas sûr de savoir définir des tableaux dynamiques, mais je vais chercher.
Et bien que j'ai déjà lu des codes avec Dictionary, je ne sais pas m'en servir et ne sait pas très bien à quoi ça sert.
Je n'ai plus qu'à étudier tout ça.
Je reviendrai si je ne m'en sors pas.

 

[Dranreb]

Pour déclarer une variable comme tableau dynamique on la fait suivre de parenthèses ouvrante et fermante sans préciser de dimension dedans.
Plus facile d'utiliser des Dictonary en cochant Microsoft Scripting Runtime dans les références disponibles, menu Outils, Références…
Ça autorise à déclarer des variables As Dictionary et dans le code des suites son proposées dans une liste en tapant un point derrière une telle variable.
Ça sert à y retrouver un truc d'après un code.

 

[Calvus]

Je déclare donc mes 2 tableaux comme suit ?
Dim tablo(), tablo2()
Et ensuite la troisième instruction uniquement.
C'est quelque chose comme ça ?
Je vais regarder les Dictionary.
Merci

 

[Dranreb]

Oui c'est ça.
Quoi la troisième instruction ? Ça : Tablo2 = [I7:AF57].Value
Les dimensions commencent à 1, UBound(Tablo2, 1) donne le nombre de lignes, UBound(Tablo2, 2) de colonnes.

 

[Calvus]

Oui, ce que j'ai appelé 3 ème instruction, c'est à partir de : 'Incrémentation des nouvelles valeurs

 

[Dranreb]

Mais attendez. Je viens de voir que vous transformez des numéros en noms, pas l'inverse !
Dans ce cas pas besoin de Dictionary. Pas besoin de boucle non plus pour retrouver le nom: le numéro y pointe directement sur la bonne ligne !

 

[Calvus]

Oui, le code fonctionne comme je le disais.
Ce qui ne fonctionne pas, c'est la mise en forme.

 

[Dranreb]

Bon j'en suis là :

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim TNoms(), PlgD As Range, TDon(), L As Long, C As Long, N As Long ' , Dic As Dictionary

Set PlgD = [I7:AF57]
If Not Application.Intersect(Target, PlgD) Is Nothing Then
'Alimente le 1er tableau des prénoms
   TNoms = [Tableau41849].Value

'Enregistrement des valeurs du tableau existant
   TDon = PlgD.Value

'Incrémentation des nouvelles valeurs
   PlgD.Interior.ColorIndex = 2
   For L = 1 To UBound(TDon, 1): For C = 1 To UBound(TDon, 2)
      If VarType(TDon(L, C)) = vbDouble Then
         N = TDon(L, C): If N > 0 And N <= UBound(TNoms, 1) Then TDon(L, C) = TNoms(N, 2)
         End If: Next C, L
   Application.EnableEvents = False
   PlgD.Value = TDon
   PlgD.SpecialCells(xlCellTypeConstants, 2).Interior.ColorIndex = 43
   Application.EnableEvents = True
   End If 'intersect
End Sub

 

[Calvus]

Ok, super.
Merci Dranreb.
En revanche, comment obtenir des couleurs différentes ?
Chaque prénom doit correspondre à une couleur différente.
C'était l'exemple à droite de la feuille, mais les valeurs ont dû s'effacer à la 1 ère exécution de ma macro, si vous l'avez exécutée.

 

[Dranreb]

Ça ne pourrait pas se faire avec des mises en forme conditionnelles ?

Sinon on va être obligé de repasser par un For Each Cel in PlgD
À la rigueur PlgD.Cells(L, C).Interior ColorIndex = …
En évitant de mettre une autre expression Range à droite, bien sûr. Par exemple 43 + N ?…

 

[Calvus]

Si. Je pensais que c'était faisable par vba et me suis entêté à chercher.
Je vais le faire par MFC.
Merci beaucoup.
Vous savez concernant ma question 2 ?
L'instruction, si elle est isolée, fonctionne, même si j'ai remarqué que la coloration se faisait en fonction des lignes et des colonnes et pas en fonction des valeurs.
Ce n'est donc pas exact, mais ce que je désirais comprendre, c'est pourquoi la même instruction est acceptée si elle est seule dans le code et pourquoi elle est refusée dans le bloc.
Je ne sais pas si c'est très clair.

 

[Dranreb]

Non, pas très clair pour moi.
Enfin pendant qu'on exploite les éléments de tableaux on peut quand même exceptionnellement faire un PlgD.Cells(L, C).Interior.ColorIndex = quelque chose

 

[Calvus]

Merci.
J'essaye de comprendre comment le code fonctionne. J'ai donc du mal à adapter.
Mais bon, je ferai les MFC de toute façon. Ca va très bien comme ça.
A bientôt