Fonction qui change la couleur du fond de la cellule si la valeur de celle-ci est différente d'un quota donné

**XceSs** · 05/04/2012, 21h27

Bonsoir, je travaille sur une fonction qui change la couleur du fond de la cellule en fonction du jour de la semaine par rapport à une date et un quota à respecter. Si la valeur de la cellule est différent du quota le fond de la cellule devient rouge.

Le problème c'est que je n'arrive pas à changer le fond de la cellule mais seulement la police avec

Code :

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

Selection.Font.ColorIndex = 3

avec

Code :

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

Selection.Interior.ColorIndex = 3

cela me met #VALEUR! ds la cellule

Plage = Cellule avec la date
Nombre = Valeur de la cellule
Quota =

Code :

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

If Nombre <> "2" Then

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
Option Explicit
 
Function JourDate(Plage As Range, Nombre As String)
    Application.Volatile True
    'numéro de jour
    Dim numerojour As Date
    Dim nj As String
    numerojour = Plage
    nj = Format(numerojour, "DDDD")
    If nj = "lundi" Then
     If Nombre <> "2" Then 'Quota
      JourDate = Nombre
      Selection.Font.ColorIndex = 3
      Else
      JourDate = Nombre
      Selection.Font.ColorIndex = 1
     End If
    ElseIf nj = "mardi" Then
     If Nombre <> "2" Then
      JourDate = Nombre
      Selection.Font.ColorIndex = 3
      Else
      JourDate = Nombre
      Selection.Font.ColorIndex = 1
     End If
    ElseIf nj = "mercredi" Then
     If Nombre <> "2" Then
      JourDate = Nombre
      Selection.Font.ColorIndex = 3
      Else
      JourDate = Nombre
      Selection.Font.ColorIndex = 1
     End If
    ElseIf nj = "jeudi" Then
     If Nombre <> "2" Then
      JourDate = Nombre
      Selection.Font.ColorIndex = 3
      Else
      JourDate = Nombre
      Selection.Font.ColorIndex = 1
     End If
    ElseIf nj = "vendredi" Then
     If Nombre <> "2" Then
      JourDate = Nombre
      Selection.Font.ColorIndex = 3
      Else
      JourDate = Nombre
      Selection.Font.ColorIndex = 1
     End If
    ElseIf nj = "samedi" Then
     If Nombre <> "2" Then
      JourDate = Nombre
      Selection.Font.ColorIndex = 3
      Else
      JourDate = Nombre
      Selection.Font.ColorIndex = 1
     End If
    ElseIf nj = "dimanche" Then
     If Nombre <> "2" Then
      JourDate = Nombre
      Selection.Font.ColorIndex = 3
      Else
      JourDate = Nombre
      Selection.Font.ColorIndex = 1
     End If
    End If
 
End Function

Merci de votre aide

**casefayere** · 05/04/2012, 23h20

Bonsoir,
et si tu remplaces "Selection" par la cellule concernée, exemple pour A :

Code :

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

Sheets("toto").Range("A1").Interior.Colorindex = 1

**XceSs** · 06/04/2012, 00h50

il y aurait il un moyen d'extraire la cellule dans laquelle est la fonction ?

genre :

JourDate(aujourdhui();2;celluleoùestcettefonction)

Merci

**Pierre Fauconnier** · 06/04/2012, 09h42

Salut.

Le plus simple ne serait-il pas d'utiliser la mise en forme conditionnelle?

**XceSs** · 06/04/2012, 14h21

J'ai pensé à ça mais par rapport à ce que je veux faire ça fonctionnera pas car je dois appliquer les MFC sur tous les jours de l'année par rapport à 10 codes horaires de travail en fonction des week end, jours fériés etc..., et faudra modifier tous les ans les MFC :s

**Pierre Fauconnier** · 06/04/2012, 14h39

Je doute qu'il n'y ait pas moyen par MFC... Cela dépend de la conception du classeur. Tu peux mettre des formules conditionnelles dans tes MFC, ce qui rend celles-ci particulièrement puissantes, notamment pour des MFC agissant sur des cellules en fonction de valeurs d'autres cellules.

Dans ma signature, tu trouveras un lien vers une vidéo qui expose la façon de travailler avec des formules dans des MFC, sur des feuilles différentes.

**XceSs** · 06/04/2012, 16h51

Le souci c'est qu'il faut que j'arrive à faire en sorte de détecter les jours fériés (lundi, mardi...) dans l'année, selon celles-ci les jours fériés changent hors j'aimerais évité de changer les MFC tous les ans

**Pierre Fauconnier** · 06/04/2012, 17h14

Il te suffit de créer un tableau avec les jours fériés et d'utiliser ce tableau, via une plage nommée, dans les formules de ta MFC.

**XceSs** · 06/04/2012, 17h54

Sinon par hasard quelqu'un pourrait me dire comment je pourrais extraire l'adresse et coordonnées de la cellule ou se trouve la fonction

Merci

**casefayere** · 06/04/2012, 18h19

Sinon par hasard quelqu'un pourrait me dire comment je pourrais extraire l'adresse et coordonnées de la cellule ou se trouve la fonction

Code :

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

msgbox plage.address

**XceSs** · 06/04/2012, 18h32

Ouaip ça je sais mais Plage n'est pas la cellule de la fonction mais la cellule ou se trouve la date à rechercher, j'ai peur que si je stipule la cellule ou est la formule ça soit en reference circulaire

**casefayere** · 06/04/2012, 18h43

bon ben !

Code :

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

msgbox selection.address

d'après ton code

**XceSs** · 06/04/2012, 19h29

Ouaip mais

Code :

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

Application.Volatile = True

j'aimerais que la formule se rafraichisse automatiquement si je remplace un employé par un autre au niveau des horaires :/ (pas chiant le XceSs)
donc est ce qu'il y a possibilité d'extraire l'adresse de la cellule ou est situé la formule sans spécifié la cellule dans la fonction (référence circulaire)

**XceSs** · 06/04/2012, 21h25

Tans pis je fais comme ça , malgré une référence circulaire :s

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
Option Explicit
 
Function JourDate(Plage As Range, Nombre As String, Cellule As Range)
    Application.Volatile True
    'numéro de jour
    Dim numerojour As Date
    Dim nj As String
    numerojour = Plage
    nj = Format(numerojour, "DDDD")
    If nj = "lundi" Then
     If Nombre <> "2" Then
      JourDate = Nombre
      Cellule.Font.ColorIndex = 3
      Else
      JourDate = Nombre
      Cellule.Font.ColorIndex = 1
     End If
    ElseIf nj = "mardi" Then
     If Nombre <> "2" Then
      JourDate = Nombre
      Cellule.Font.ColorIndex = 3
      Else
      JourDate = Nombre
      Cellule.Font.ColorIndex = 1
     End If
    ElseIf nj = "mercredi" Then
     If Nombre <> "2" Then
      JourDate = Nombre
      Cellule.Font.ColorIndex = 3
      Else
      JourDate = Nombre
      Cellule.Font.ColorIndex = 1
     End If
    ElseIf nj = "jeudi" Then
     If Nombre <> "2" Then
      JourDate = Nombre
      Cellule.Font.ColorIndex = 3
      Else
      JourDate = Nombre
      Cellule.Font.ColorIndex = 1
     End If
    ElseIf nj = "vendredi" Then
     If Nombre <> "2" Then
      JourDate = Nombre
      Cellule.Font.ColorIndex = 3
      Else
      JourDate = Nombre
      Cellule.Font.ColorIndex = 1
     End If
    ElseIf nj = "samedi" Then
     If Nombre <> "2" Then
      JourDate = Nombre
      Cellule.Font.ColorIndex = 3
      Else
      JourDate = Nombre
      Cellule.Font.ColorIndex = 1
     End If
    ElseIf nj = "dimanche" Then
     If Nombre <> "2" Then
      JourDate = Nombre
      Cellule.Font.ColorIndex = 3
      Else
      JourDate = Nombre
      Cellule.Font.ColorIndex = 1
     End If
    End If
 
End Function

**Pierre Fauconnier** · 07/04/2012, 06h46

1. Tu ne peux pas modifier l'aspect de la feuille au travers d'une fonction utilisée dans la feuille. Autrement dit, tu ne peux pas avoir cellule.interior.colorindex= ... dans une fonction de feuille de calcul. Si tu fais cela, tu recevras un #VALEUR! ;

2. Tu ne peux effectivement pas, dans une fonction de feuille de calcul, passer en paramètre la cellule qui utilise la fonction, sauf à te prendre une référence circulaire ;

3. Outre le fait qu'une MFC est LA solution à ton problème, ton code est mal construit. Plutôt que des

Code :

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

1
2
3
4
5
6
if ... then
    ...    
    elseif
    ...
    ...
End If

Utilise

Code :

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

1
2
3
4
5
6
Select case nj
    case "lundi"
        ...
    case "mardi"
        ...
End Case

4. Pourquoi tester les jours de la semaine alors que ta fonction fait exactement la même chose pour chaque jour

. Tel que tu l'a donné, ton code peut se réduire à

Code :

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

1
2
3
4
5
6
     If Nombre <> "2" Then
      JourDate = Nombre
      Cellule.Font.ColorIndex = 3
      Else
      JourDate = Nombre
      Cellule.Font.ColorIndex = 1

5. Ton code ne fonctionnera que sur des versions francophones, car en anglais, lundi => monday. Tu aurais donc intérêt, d'une façon générale, à utiliser le numéro du jour dans la semaine plutôt que le nom du jour.

Code :

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

1
2
3
4
5
6
7
nj = weekday(plage,2) ' 2 signifie que tu souhaites que lundi = 1
select case nj
    case 1
        ...
    case 2
        ...
end case

6. Tu mets ta limite de quota en dur (hard coding) dans ta fonction. Si cette valeur change, tu vas devoir modifier ta fonction.

7. Tu dis

Envoyé par XceSs

Le souci c'est qu'il faut que j'arrive à faire en sorte de détecter les jours fériés (lundi, mardi...) dans l'année, selon celles-ci les jours fériés changent hors j'aimerais évité de changer les MFC tous les ans

Dans ta fonction, je ne vois pas de prise en compte de jours fériés, puisque ton code est identique pour chaque jour de la semaine, sans prise en compte d'une plage de jours fériés. Où comptes-tu renseigner les jours fériés d'une année? Dans ton code en hard coding comme pour le dépassement de quota? Tu devras donc changer ta fonction tous les ans.

8. Par MFC, voici un exemple qui allume les valeurs lorsque la date correspond à un jour de semaine (hors samedi et dimanche), que la valeur dépasse un quota et que la date n'est pas un jour férié, renseigné dans une plage spécifique et nommée. Comme je l'explique dans ma vidéo, il est préférable, depuis XL2007, de travailler avec des tableaux puis de nommer la plage de données du tableau pour pouvoir l'utiliser en MFC. De cette manière, un jour férié ajouté est automatiquement pris en compte dans la plage. Idéalement, la plage des jours fériés est sur une autre feuille que celle de la saisie, dans une bonne conception de classeur. Tu peux donc gérer plusieurs années sans devoir modifier la MFC.

Dans la solution illustrée ici, il faut noter que la plage B2:B21 est sélectionnée avec B2 active pour la saisie de la formule dans la MFC.

Pourquoi donc s'entêter à ne pas essayer les solutions proposées lorsque l'on demande de l'aide?

**XceSs** · 07/04/2012, 09h34

Pour l'histoire des quotas, effectivement dans la fonction ils font tous la même action car je la teste mais en fait chaque jour de la semaine aura un quota différent 1, 2, 3...

Je vais essayer de faire comme tu dis pour le reste

Merci

Fonction qui change la couleur du fond de la cellule si la valeur de celle-ci est différente d'un quota donné [XL-2007]

Macros et VBA Excel

Discussions similaires

Partager

Partager