Discussion :

[wyzer]

Bonjour,

Comment je pourrais faire pour que ma macro me donne le nom de la couleur, et non le code couleur svp ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A1").Select
ActiveCell.FormulaR1C1 = "Couleur"
Columns("A:A").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
 
Dim c As Variant
 
For Each c In Selection
 
c.Value = c.Offset(0, 1).Interior.Color
c.Interior.Color = c.Offset(0, 1).Interior.Color
 
Next c
 
Rows("1:1").Select
Selection.AutoFilter
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select

Merci par avance

[Theze]

Bonjour,

Adapte à tes besoins :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
 
Sub NomCouleurs()
 
    Dim Tbl
    Dim I As Integer
    Dim J As Integer
 
    'nom des couleurs (j'en ai défini quelques unes, à adapter si le nom ne convient pas)
    Tbl = Array("Noir", "Blanc", "Rouge", "Vert brillant", "Bleu", "Jaune", _
                "Rose", "Turquoise", "Rouge foncé", "Vert", "Bleu foncé", _
                "Marron clair", "Violet", "Bleu-Vert", "Gris-25%", "Gris-50%", _
                "Lavande foncé", "Jaune pale", "Prune foncé", "Saumon foncé", _
                "Bleu Gris", "Mauve clair", "Bleu ciel", "Turquoise clair", _
                "Vert clair", "Jaune clair", "Bleu moyen", "Saumon", "Lavande", _
                "Brun", "Bleu clair", "Vert d'eau", "Citron Vert", "Or", _
                "Orange clair", "Orange", "Bleu Gris", "Gris-40%", _
                "Bleu-vert foncé", "Vert marin", "Vert foncé", "Vert olive", _
                "Marron", "Prune", "Indigo", "Gris-80%")
 
    For I = 1 To 56
 
        Select Case I
 
            'certaines étant identiques, j'évite les doublons
            Case 1 To 17, 19, 21 To 24, 33 To 56
 
            J = J + 1
            'le nom dans la cellule A et la couleur dans la cellule B
            Range("A" & J) = Tbl(J - 1)
            Range("B" & J).Interior.ColorIndex = I
 
        End Select
 
    Next I
 
End Sub

Un petit tes pour voir qu'il y en a en double :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
 
Sub CouleurRVB()
 
    Dim Couleur As Long
    Dim I As Long, J As Long
    Dim Code As String
 
    For I = 1 To 56
 
        'colore la cellule A
        Range("A" & I).Interior.ColorIndex = I
 
        Code = ""
        'récupère son code
        Couleur = Range("A" & I).Interior.Color
 
        'défini le code RVB
        For J = 2 To 0 Step -1
             Code = Code & Couleur \ 256 ^ J & "-"
             Couleur = Couleur Mod 256 ^ J
        Next J
 
        'l'inscrit dan la cellule B
        Range("B" & I) = Left(Code, Len(Code) - 1)
 
    Next I
 
    [C1].FormulaLocal = "=NB.SI($B$1:$B$56;B1)"
    [C1].AutoFill [C1:C56]
 
End Sub

Hervé.

[wyzer]

Merci beaucoup !!

[wyzer]

Pour ceux que ça interesse voici mon adaptation :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
 
Sub Filtrer_par_Couleur()
Application.ScreenUpdating = False
 
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A1").Select
ActiveCell.FormulaR1C1 = "Couleur"
Columns("A:A").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
 
Dim c As Variant
 
For Each c In Selection
 
c.Value = c.Offset(0, 1).Interior.Color
 
Next c
 
For Each c In Selection
 
Select Case c
 
Case Is = "0": c.Value = "Noir"
Case Is = "13209": c.Value = "Marron"
Case Is = "13107": c.Value = "Vert olive"
Case Is = "13056": c.Value = "Vert foncé"
Case Is = "6697728": c.Value = "Bleu-vert foncé"
Case Is = "8388608": c.Value = "Bleu foncé"
Case Is = "10040115": c.Value = "Indigo"
Case Is = "3355443": c.Value = "Gris -80%"
Case Is = "128": c.Value = "Rouge foncé"
Case Is = "26367": c.Value = "Orange"
Case Is = "32896": c.Value = "Marron clair"
Case Is = "32768": c.Value = "Vert"
Case Is = "8421376": c.Value = "Bleu-vert"
Case Is = "16711680": c.Value = "Bleu"
Case Is = "10053222": c.Value = "Bleu gris"
Case Is = "8421504": c.Value = "Gris -50%"
Case Is = "255": c.Value = "Rouge"
Case Is = "39423": c.Value = "Orange clair"
Case Is = "52377": c.Value = "Citron vert"
Case Is = "6723891": c.Value = "Vert marin"
Case Is = "13421619": c.Value = "Vert d'eau"
Case Is = "16737843": c.Value = "Bleu clair"
Case Is = "8388736": c.Value = "Violet"
Case Is = "9868950": c.Value = "Gris -40%"
Case Is = "16711935": c.Value = "Rose"
Case Is = "52479": c.Value = "Or"
Case Is = "65535": c.Value = "Jaune"
Case Is = "65280": c.Value = "Vert brillant"
Case Is = "16776960": c.Value = "Turquoise"
Case Is = "16763904": c.Value = "Bleu ciel"
Case Is = "6697881": c.Value = "Prune"
Case Is = "12632256": c.Value = "Gris -25%"
Case Is = "13408767": c.Value = "Rose saumon"
Case Is = "14935011": c.Value = "Jeu de couleurs"
Case Is = "10092543": c.Value = "Jaune clair"
Case Is = "13434828": c.Value = "Vert clair"
Case Is = "16777164": c.Value = "Turquoise clair"
Case Is = "16764057": c.Value = "Bleu moyen"
Case Is = "16751052": c.Value = "Lavande"
Case Is = "16777215": c.Value = "Blanc"
 
End Select
 
Next c
 
Rows("1:1").Select
Selection.AutoFilter
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select
 
Application.ScreenUpdating = True
End Sub