rech d'une date et colorié la cellule

[Sylvie64]

Bonjour,

Sur feuille Excel de plus de 85000 lignes est-il possible de colorié une cellule qui
comporte une date?
- exemple : coloriés toutes les cellules qui sont de 1940 et après ...

Et en suite de pouvoir faire une recherche de cette cellule colorié ?

Voir PJ, l'exemple des cellules en jaune.

Merci

Sylvie

 

[JNP]

Re : rech d'une date et colorié la cellule

Bonjour Sylvie 🙂,
Problème sympa 😛...
Questions :
1) E6 contient 1901 et 1983, donc on cherche "au moins une date supérieure à 1940 ?
2) A priori, tes dates sont en jj.mm.aaaa ou aaaa, y-a-t'il d'autres formats ? Et n'y-a-t'il jamais 4 chiffres qui seraient autre chose qu'une date ?
3) Colorer, puis recherche... Ce ne serait pas plus simple de faire juste la recherche ?
A te lire 😎

 

[JNP]

Re : rech d'une date et colorié la cellule

Re 🙂,
En attendant ta réponse, un premier code qui met en orange les cellules concernées

Sub Test()
Dim Cellule As Range, Match, Matches
For Each Cellule In Cells.SpecialCells(xlCellTypeConstants)
With CreateObject("vbscript.regexp")
    .Global = True:
    .Pattern = "\b\d{4}\b"
    Set Matches = .Execute(Cellule)
    For Each Match In Matches
        If CDbl(Match) > 1940 Then
            Cellule.Interior.ColorIndex = 44
        End If
    Next
    .Pattern = "\b\d{2}\.\d{2}\.\d{4}\b"
    Set Matches = .Execute(Cellule)
    For Each Match In Matches
        If CDate(Replace(Match, ".", "/")) > #1/1/1940# Then
            Cellule.Interior.ColorIndex = 44
        End If
    Next
End With
Next
End Sub

Bon WE 😎

 

[Tibo]

Re : rech d'une date et colorié la cellule

Bonjour,

En considérant que les nombres à 4 chiffres sont des dates (et rien d'autre), on peut appliquer une Mise en Forme Conditionnelle.

Sélection de A1:M50

Mise en forme conditionnelle - La formule est :

=SOMME(SI(ESTNUM(STXT(SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;".";"µ");" ";"µ");"/";
"µ")&"µ";LIGNE(INDIRECT("1:"&NBCAR(A1)));4)*1);((STXT(SUBSTITUE(SUBSTITUE(A1;
".";"µ");" ";"µ")&"µ";LIGNE(INDIRECT("1:"&NBCAR(A1)));4)*1>1940)*1)))

+ choix couleur

Y a peut-être (sans doute) plus simple

Quant à faire la recherche des cellules concernées par formule, je n'ai pas osé me lancer ...

@+

Edit : salut Jean-Noël 🙂

@+

 

[JNP]

Re : rech d'une date et colorié la cellule

Re 🙂,

Y a peut-être (sans doute) plus simple

Peut-être, mais là, j'ai rien compris à ta formule 😕...
Et pourtant, elle est aussi efficace que ma macro 😛...
Si tu peux m'expliquer ce que les µ apportent... et un peu du raisonnement, merci d'avance 😉.
Bonne soirée 😎

 

[Tibo]

Re : rech d'une date et colorié la cellule

Salut Jean-Noël 🙂,

les SUBSTITUE servent à remplacer les espaces, les points et les / par un caractère texte quelconque (ici : µ)

Les espaces : ils sont neutres au début ou à la fin d'un chaine pour déterminer si cette chaine est numérique ou pas

les points : car j'utilise le . comme séparateur décimal

les / : Excel reconnait une date alors que je ne cherche qu'un nombre de 4 chiffres.

Ensuite, avec LIGNE(INDIRECT("1:"&NBCAR(A1)));4) associé à STXT, je génère une matrice de chaines de 4 caractères et cherche s'il y a des chaines composées de 4 chiffres.

Reste à tester si parmi ces chaines (numériques), une ou plusieurs sont supérieures à 1940

Voilou pour la tentative d'explication.

@+

 

[david84]

Re : rech d'une date et colorié la cellule

Bonjour Sylvie, Salut Tibo🙂, JNP🙂
ci-joint macro à tester faite en collaboration avec étroite avec JNP 😀 :

 Sub TestFinal()
Dim Cellule As Range, Cible As String, I As Integer
For Each Cellule In cells.SpecialCells(xlCellTypeConstants)
    Cible = Cellule.Text
    For I = 1 To Len(Cible)
        If IsNumeric(Mid(Cible, I, 1)) Then
            If Len(CStr(Val(Replace(Mid(Cible, I, Len(Cible) - I + 1), ".", "")))) > 3 Then

                 If CDbl(Right(Val(Replace(Mid(Cible, I, Len(Cible) - I + 1), ".", "")), 4)) _
                    > 1940 Then Cellule.Interior.ColorIndex = 44
                I = I + Len(CStr(Val(Replace(Mid(Cible, I, Len(Cible) - I + 1), ".", "")))) + 2
            End If
        End If
    Next I
Next
End Sub

A+

 

[JNP]

Re : rech d'une date et colorié la cellule

Re 🙂,

les SUBSTITUE servent à remplacer les espaces, les points et les / par un caractère texte quelconque (ici : µ)

Les espaces : ils sont neutres au début ou à la fin d'un chaine pour déterminer si cette chaine est numérique ou pas

les points : car j'utilise le . comme séparateur décimal

les / : Excel reconnait une date alors que je ne cherche qu'un nombre de 4 chiffres.

Ensuite, avec LIGNE(INDIRECT("1:"&NBCAR(A1)));4) associé à STXT, je génère une matrice de chaines de 4 caractères et cherche s'il y a des chaines composées de 4 chiffres.

Reste à tester si parmi ces chaines (numériques), une ou plusieurs sont supérieures à 1940

Oui, surtout qu'en plus, avec David (qui est issu des formules 😛...) on a essayé finalement le même raisonnement 😀...
De nos questionnement sort que le RegExp est plus carré, mais cette approche est tout aussi efficace 😉.
Merci de tes explications et bon WE 😎

 

[Sylvie64]

Re : rech d'une date et colorié la cellule

Bonjour à tous,

Merci de vous être penché sur mon problème.

Bonjour Sylvie 🙂,
Problème sympa 😛...
Questions :
1) E6 contient 1901 et 1983, donc on cherche "au moins une date supérieure à 1940 ?
2) A priori, tes dates sont en jj.mm.aaaa ou aaaa, y-a-t'il d'autres formats ? Et n'y-a-t'il jamais 4 chiffres qui seraient autre chose qu'une date ?
3) Colorer, puis recherche... Ce ne serait pas plus simple de faire juste la recherche ?
A te lire 😎

JNP :
1) OUI
2) NON
3) OUI MAIS COMMENT ?

Le code fonctionne bien, super !!!

TIBO :
Je n'avais jamais mis un code Mise en forme conditionnelle (je sais je suis pas douée 😀) je suis allée dans "accueil, cliqué sur mise en forme conditionnelle, règles de mise en surbrillance des cellules et supérieur à" j'ai mise 1940 avec la couleur mais TOUTE LA PAGE SE COLORISE !!!! alalalala j'ai fais quelques chose ou pas !!! depuis hier je ne trouve et pas, pas faute de ne pas avoir chercher !!!

david84 :
Ton code fonctionne, merci beaucoup

Merci à tout les trois de votre aide

Sylvie

 

[Tibo]

Re : rech d'une date et colorié la cellule

Bonjour Sylvie,

Pour appliquer la MFC, il faut préalablement sélectionner les cellules concernées. Ici : A1:M50

Ensuite, Groupe Accueil - Mise en forme conditionnelle - Nouvelle règle - Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué -

en dessous, coller la formule proposée

dans Format, choisir une couleur

puis OK

Je te joins ton fichier avec la MFC

Cela dit, compte tenu de la relative complexité de la formule, je préconise le recours aux solutions VBA de nos amis

@+

 

[JNP]

Re : rech d'une date et colorié la cellule

Re 🙂,

JNP :
3) OUI MAIS COMMENT ?

Une proposition qui devrait te plaire 😛...
Tu ouvres le fichier joint, tu fais Ctrl+f, et après, il suffit de cliquer sur la ligne qui t'intéresse dans l'USF 😀...
Bon dimanche 😎

 

[Sylvie64]

Re : rech d'une date et colorié la cellule

Bonjour,

Tibo : merci pour le chemin, en fait, je l'avais effectué mais le souci j'avais laissé la même couleur donc cela ne pouvez pas aller !!!!🙄

JNP : trop coollllllllllll mais comment effectué ce module ? je l'ai installé mais quand je fais ctrl+f et bien c'est "reche et rempl" qui apparait pas le tableau comme sur le fichier envoyé !!!!

Mille merci a vous

Sylvie

 

[JNP]

Re : rech d'une date et colorié la cellule

Re 🙂,
Tu fais Exécuter une macro, tu sélectionnes MiseEnForme. Là tu as Options et tu lui affecte le raccourci Ctrl+f 😉.
Bon courage 😎

 

[Sylvie64]

Re : rech d'une date et colorié la cellule

Re 🙂,
Tu fais Exécuter une macro, tu sélectionnes MiseEnForme. Là tu as Options et tu lui affecte le raccourci Ctrl+f 😉.
Bon courage 😎

Merci, mais je ne trouve pas la MiseEnForme !!!
Faut-il cocher quelque chose pour la voir apparaitre ?

Aussi, qui me dit dans le code qu'il faut colorié la cellule a partir de cette date ?
Car si je veux changer la date 1940 en 1935 comment dois je faire ?

Merci

Sylvie

 

[JNP]

Re : rech d'une date et colorié la cellule

Re 🙂,

Merci, mais je ne trouve pas la MiseEnForme !!!
Faut-il cocher quelque chose pour la voir apparaitre ?
Aussi, qui me dit dans le code qu'il faut colorié la cellule a partir de cette date ?
Car si je veux changer la date 1940 en 1935 comment dois je faire ?

De 2 choses l'une :
Soit tu copies tes données dans mon fichier et tout fonctionne 😛...
Soit tu copies les codes, les modules et les USF de mon fichier dans ton fichier et tu complètes par l'association au raccourci clavier 😀...

Pour avoir le choix dans la date (contrepèterie bien connue 😱), modifie le code ainsi

Sub MiseEnForme()
Dim Cellule As Range, Match, Matches, MaDate As String
MaDate = InputBox("Date de recherche ? (aaaa)", "Date minimum")
If Len(MaDate) <> 4 Or Not IsNumeric(MaDate) Then Exit Sub
For Each Cellule In Cells.SpecialCells(xlCellTypeConstants)
Cellule.Interior.ColorIndex = 2
With CreateObject("vbscript.regexp")
    .Global = True:
    .Pattern = "\b\d{4}\b"
    Set Matches = .Execute(Cellule)
    For Each Match In Matches
        If CDbl(Match) > CDbl(MaDate) Then
            Cellule.Interior.ColorIndex = 44
        End If
    Next
    .Pattern = "\b\d{2}\.\d{2}\.\d{4}\b"
    Set Matches = .Execute(Cellule)
    For Each Match In Matches
        If CDate(Replace(Match, ".", "/")) > CDate("01/01/" & MaDate) Then
            Cellule.Interior.ColorIndex = 44
        End If
    Next
End With
Next
Recherche.Show
End Sub

Bon courage 😎