Comment mettre une macro existante dans un bouton

Créer une discussion
Répondre
  • Initiateur de la discussion Initiateur de la discussion highlander
  • Date de début Date de début

Boostez vos compétences Excel avec notre communauté !

Rejoignez Excel Downloads, le rendez-vous des passionnés où l'entraide fait la force. Apprenez, échangez, progressez – et tout ça gratuitement ! 👉 Inscrivez-vous maintenant !

S'inscrire
H

highlander

Guest
bonsoir,

J'ai créer un fichier excell dans lequel une macro mise dans "Thisworkbook" me permet de mettre en couleur certains mots dans une feuille excell. Et ce pour toute les feuilles.

Cependant, cela ralenti enormement la saisie des informations dans mon classeur.

Je voudrais donc pouvoir noter les noms et après en appuyant sur un bouton appliquer la couleur

J'ai créer un bouton et coller la macro du thisworkbook dedans. mais cela ne marche pas

si quelqu'un peut m'aider.

merci d'avance
 
Trier par date Trier par votes
Re : Comment mettre une macro existante dans un bouton

Bonsoir,

Que les Vbaïstes de ce forum puissent t'aider je n'en doute pas un instant, mais faut quand même reconnaître que sans code et sans fichier joint... tu ne leur facilite pas la tâche !!!

Bonne soirée ...

mth
 
Vote positif 0 Vote négatif
Re : Comment mettre une macro existante dans un bouton

Oui désolé ai oublier le code

Voici le code du Thisworkbook



Code: 
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Application.EnableEvents = False
Application.ScreenUpdating = False
    If Not Intersect(Target, Range("C4:BM42")) Is Nothing Then
        For Each cell In Target
            If cell = Range("B84") Then Target = Range("A84")
Cells.Replace What:=("B84"), Replacement:=("A84"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B85") Then Target = Range("A85")
Cells.Replace What:=("B85"), Replacement:=("A85"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B86") Then Target = Range("A86")
Cells.Replace What:=("B86"), Replacement:=("A86"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B87") Then Target = Range("A87")
Cells.Replace What:=("B87"), Replacement:=("A87"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B88") Then Target = Range("A88")
Cells.Replace What:=("B88"), Replacement:=("A88"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B89") Then Target = Range("A89")
Cells.Replace What:=("B89"), Replacement:=("A89"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B90") Then Target = Range("A90")
Cells.Replace What:=("B90"), Replacement:=("A90"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B91") Then Target = Range("A91")
Cells.Replace What:=("B91"), Replacement:=("A91"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B92") Then Target = Range("A92")
Cells.Replace What:=("B92"), Replacement:=("A92"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B93") Then Target = Range("A93")
Cells.Replace What:=("B93"), Replacement:=("A93"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B94") Then Target = Range("A94")
Cells.Replace What:=("B94"), Replacement:=("A94"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B95") Then Target = Range("A95")
Cells.Replace What:=("B95"), Replacement:=("A95"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B96") Then Target = Range("A96")
Cells.Replace What:=("B96"), Replacement:=("A96"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B97") Then Target = Range("A97")
Cells.Replace What:=("B97"), Replacement:=("A97"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B98") Then Target = Range("A98")
Cells.Replace What:=("B98"), Replacement:=("A98"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B99") Then Target = Range("A99")
Cells.Replace What:=("B99"), Replacement:=("A99"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B100") Then Target = Range("A100")
Cells.Replace What:=("B100"), Replacement:=("A100"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B101") Then Target = Range("A101")
Cells.Replace What:=("B101"), Replacement:=("A91"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B102") Then Target = Range("A102")
Cells.Replace What:=("B102"), Replacement:=("A102"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B103") Then Target = Range("A103")
Cells.Replace What:=("B103"), Replacement:=("A103"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B104") Then Target = Range("A104")
Cells.Replace What:=("B104"), Replacement:=("A104"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B105") Then Target = Range("A105")
Cells.Replace What:=("B105"), Replacement:=("A105"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B106") Then Target = Range("A106")
Cells.Replace What:=("B106"), Replacement:=("A106"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B107") Then Target = Range("A107")
Cells.Replace What:=("B107"), Replacement:=("A107"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B108") Then Target = Range("A108")
Cells.Replace What:=("B108"), Replacement:=("A108"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B109") Then Target = Range("A109")
Cells.Replace What:=("B109"), Replacement:=("A109"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B110") Then Target = Range("A110")
Cells.Replace What:=("B110"), Replacement:=("A110"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B111") Then Target = Range("A111")
Cells.Replace What:=("B111"), Replacement:=("A111"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B112") Then Target = Range("A112")
Cells.Replace What:=("B12"), Replacement:=("A112"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B113") Then Target = Range("A113")
Cells.Replace What:=("B113"), Replacement:=("A113"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B114") Then Target = Range("A114")
Cells.Replace What:=("B114"), Replacement:=("A114"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B115") Then Target = Range("A115")
Cells.Replace What:=("B115"), Replacement:=("A115"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B116") Then Target = Range("A116")
Cells.Replace What:=("B116"), Replacement:=("A116"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B117") Then Target = Range("A117")
Cells.Replace What:=("B117"), Replacement:=("A117"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B118") Then Target = Range("A118")
Cells.Replace What:=("B118"), Replacement:=("A118"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B119") Then Target = Range("A119")
Cells.Replace What:=("B119"), Replacement:=("A119"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B120") Then Target = Range("A120")
Cells.Replace What:=("B120"), Replacement:=("A120"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B121") Then Target = Range("A121")
Cells.Replace What:=("B121"), Replacement:=("A121"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B122") Then Target = Range("A122")
Cells.Replace What:=("B122"), Replacement:=("A122"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B123") Then Target = Range("A123")
Cells.Replace What:=("B123"), Replacement:=("A123"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B124") Then Target = Range("A124")
Cells.Replace What:=("B124"), Replacement:=("A124"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B125") Then Target = Range("A125")
Cells.Replace What:=("B125"), Replacement:=("A125"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B126") Then Target = Range("A126")
Cells.Replace What:=("B126"), Replacement:=("A126"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B127") Then Target = Range("A127")
Cells.Replace What:=("B127"), Replacement:=("A127"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B128") Then Target = Range("A128")
Cells.Replace What:=("B128"), Replacement:=("A128"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B129") Then Target = Range("A129")
Cells.Replace What:=("B129"), Replacement:=("A129"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B130") Then Target = Range("A130")
Cells.Replace What:=("B130"), Replacement:=("A130"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B131") Then Target = Range("A131")
Cells.Replace What:=("B131"), Replacement:=("A131"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B132") Then Target = Range("A132")
Cells.Replace What:=("B132"), Replacement:=("A132"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B133") Then Target = Range("A133")
Cells.Replace What:=("B133"), Replacement:=("A133"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B134") Then Target = Range("A134")
Cells.Replace What:=("B134"), Replacement:=("A134"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B135") Then Target = Range("A135")
Cells.Replace What:=("B135"), Replacement:=("A135"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B136") Then Target = Range("A136")
Cells.Replace What:=("B136"), Replacement:=("A136"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B137") Then Target = Range("A137")
Cells.Replace What:=("B137"), Replacement:=("A137"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B138") Then Target = Range("A138")
Cells.Replace What:=("B138"), Replacement:=("A138"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B139") Then Target = Range("A139")
Cells.Replace What:=("B139"), Replacement:=("A139"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B140") Then Target = Range("A140")
Cells.Replace What:=("B140"), Replacement:=("A140"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
             If cell = Range("B141") Then Target = Range("A141")
Cells.Replace What:=("B141"), Replacement:=("A141"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B142") Then Target = Range("A142")
Cells.Replace What:=("B142"), Replacement:=("A142"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B143") Then Target = Range("A143")
Cells.Replace What:=("B143"), Replacement:=("A143"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
         Next
            End If
If Not Intersect(Target, Range("C4:BM42")) Is Nothing Then
    For Each cell In Target
        If Range("A83") = cell.Value Then
            cell.Interior.ColorIndex = xlNone
        ElseIf Range("A84") = cell.Value Then
            cell.Interior.ColorIndex = 53
        ElseIf Range("A85") = cell.Value Then
            cell.Interior.ColorIndex = 4
        ElseIf Range("A88") = cell.Value Then
            cell.Interior.ColorIndex = 34
        ElseIf Range("A87") = cell.Value Then
            cell.Interior.ColorIndex = 33
        ElseIf Range("A86") = cell.Value Then
            cell.Interior.ColorIndex = 39
        ElseIf Range("A89") = cell.Value Then
            cell.Interior.ColorIndex = 25
            cell.Font.ColorIndex = 2
        ElseIf Range("A90") = cell.Value Then
            cell.Interior.ColorIndex = 10
        ElseIf Range("A99") = cell.Value Then
            cell.Interior.ColorIndex = 5
            cell.Font.ColorIndex = 2
        ElseIf Range("A92") = cell.Value Then
            cell.Interior.ColorIndex = 7
        ElseIf Range("A93") = cell.Value Then
            cell.Interior.ColorIndex = 12
        ElseIf Range("A94") = cell.Value Then
            cell.Interior.ColorIndex = 56
            cell.Font.ColorIndex = 2
        ElseIf Range("A95") = cell.Value Then
            cell.Interior.ColorIndex = 8
        ElseIf Range("A96") = cell.Value Then
            cell.Interior.ColorIndex = 6
        ElseIf Range("A97") = cell.Value Then
            cell.Interior.ColorIndex = 9
            cell.Font.ColorIndex = 2
        ElseIf Range("A98") = cell.Value Then
            cell.Interior.ColorIndex = 1
            cell.Font.ColorIndex = 3
        ElseIf Range("A91") = cell.Value Then
            cell.Interior.ColorIndex = 43
        ElseIf Range("A100") = cell.Value Then
            cell.Interior.ColorIndex = 50
        ElseIf Range("A101") = cell.Value Then
            cell.Interior.ColorIndex = 38
        ElseIf Range("A102") = cell.Value Then
            cell.Interior.ColorIndex = 35
        ElseIf Range("A103") = cell.Value Then
            cell.Interior.ColorIndex = 40
        ElseIf Range("A104") = cell.Value Then
            cell.Interior.ColorIndex = 44
        ElseIf Range("A105") = cell.Value Then
            cell.Interior.ColorIndex = 14
        ElseIf Range("A106") = cell.Value Then
            cell.Interior.ColorIndex = 17
        ElseIf Range("A107") = cell.Value Then
            cell.Interior.ColorIndex = 18
        ElseIf Range("A108") = cell.Value Then
            cell.Interior.ColorIndex = 19
        ElseIf Range("A109") = cell.Value Then
            cell.Interior.ColorIndex = 22
        ElseIf Range("A110") = cell.Value Then
            cell.Interior.ColorIndex = 24
        ElseIf Range("A111") = cell.Value Then
            cell.Interior.ColorIndex = 36
        ElseIf Range("A112") = cell.Value Then
            cell.Interior.ColorIndex = 46
        ElseIf Range("A113") = cell.Value Then
            cell.Interior.ColorIndex = 47
        ElseIf Range("A114") = cell.Value Then
            cell.Interior.ColorIndex = 53
        ElseIf Range("A115") = cell.Value Then
            cell.Interior.ColorIndex = 4
        ElseIf Range("A118") = cell.Value Then
            cell.Interior.ColorIndex = 34
        ElseIf Range("A117") = cell.Value Then
            cell.Interior.ColorIndex = 33
        ElseIf Range("A116") = cell.Value Then
            cell.Interior.ColorIndex = 39
        ElseIf Range("A119") = cell.Value Then
            cell.Interior.ColorIndex = 25
            cell.Font.ColorIndex = 2
        ElseIf Range("A120") = cell.Value Then
            cell.Interior.ColorIndex = 10
        ElseIf Range("A129") = cell.Value Then
            cell.Interior.ColorIndex = 5
            cell.Font.ColorIndex = 2
        ElseIf Range("A122") = cell.Value Then
            cell.Interior.ColorIndex = 7
        ElseIf Range("A123") = cell.Value Then
            cell.Interior.ColorIndex = 12
        ElseIf Range("A124") = cell.Value Then
            cell.Interior.ColorIndex = 56
            cell.Font.ColorIndex = 2
        ElseIf Range("A125") = cell.Value Then
            cell.Interior.ColorIndex = 8
        ElseIf Range("A126") = cell.Value Then
            cell.Interior.ColorIndex = 6
        ElseIf Range("A127") = cell.Value Then
            cell.Interior.ColorIndex = 9
            cell.Font.ColorIndex = 2
        ElseIf Range("A128") = cell.Value Then
            cell.Interior.ColorIndex = 1
            cell.Font.ColorIndex = 3
        ElseIf Range("A121") = cell.Value Then
            cell.Interior.ColorIndex = 43
        ElseIf Range("A130") = cell.Value Then
            cell.Interior.ColorIndex = 50
        ElseIf Range("A131") = cell.Value Then
            cell.Interior.ColorIndex = 38
        ElseIf Range("A132") = cell.Value Then
            cell.Interior.ColorIndex = 35
        ElseIf Range("A133") = cell.Value Then
            cell.Interior.ColorIndex = 40
        ElseIf Range("A134") = cell.Value Then
            cell.Interior.ColorIndex = 44
        ElseIf Range("A135") = cell.Value Then
            cell.Interior.ColorIndex = 14
        ElseIf Range("A136") = cell.Value Then
            cell.Interior.ColorIndex = 17
        ElseIf Range("A137") = cell.Value Then
            cell.Interior.ColorIndex = 18
        ElseIf Range("A138") = cell.Value Then
            cell.Interior.ColorIndex = 19
        ElseIf Range("A139") = cell.Value Then
            cell.Interior.ColorIndex = 22
        ElseIf Range("A140") = cell.Value Then
            cell.Interior.ColorIndex = 24
        ElseIf Range("A141") = cell.Value Then
            cell.Interior.ColorIndex = 36
        ElseIf Range("A142") = cell.Value Then
            cell.Interior.ColorIndex = 46
        ElseIf Range("A143") = cell.Value Then
            cell.Interior.ColorIndex = 47
        End If
    Next
        End If


    If Not Intersect(Target, Range("BO4:BO42")) Is Nothing Then
        For Each cell In Target
            If cell = Range("B84") Then Target = Range("A84")
Cells.Replace What:=("B84"), Replacement:=("A84"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B85") Then Target = Range("A85")
Cells.Replace What:=("B85"), Replacement:=("A85"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B86") Then Target = Range("A86")
Cells.Replace What:=("B86"), Replacement:=("A86"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B87") Then Target = Range("A87")
Cells.Replace What:=("B87"), Replacement:=("A87"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B88") Then Target = Range("A88")
Cells.Replace What:=("B88"), Replacement:=("A88"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B89") Then Target = Range("A89")
Cells.Replace What:=("B89"), Replacement:=("A89"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B90") Then Target = Range("A90")
Cells.Replace What:=("B90"), Replacement:=("A90"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B91") Then Target = Range("A91")
Cells.Replace What:=("B91"), Replacement:=("A91"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B92") Then Target = Range("A92")
Cells.Replace What:=("B92"), Replacement:=("A92"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B93") Then Target = Range("A93")
Cells.Replace What:=("B93"), Replacement:=("A93"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B94") Then Target = Range("A94")
Cells.Replace What:=("B94"), Replacement:=("A94"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B95") Then Target = Range("A95")
Cells.Replace What:=("B95"), Replacement:=("A95"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B96") Then Target = Range("A96")
Cells.Replace What:=("B96"), Replacement:=("A96"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B97") Then Target = Range("A97")
Cells.Replace What:=("B97"), Replacement:=("A97"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B98") Then Target = Range("A98")
Cells.Replace What:=("B98"), Replacement:=("A98"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B99") Then Target = Range("A99")
Cells.Replace What:=("B99"), Replacement:=("A99"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B100") Then Target = Range("A100")
Cells.Replace What:=("B100"), Replacement:=("A100"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B101") Then Target = Range("A101")
Cells.Replace What:=("B101"), Replacement:=("A91"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B102") Then Target = Range("A102")
Cells.Replace What:=("B102"), Replacement:=("A102"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B103") Then Target = Range("A103")
Cells.Replace What:=("B103"), Replacement:=("A103"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B104") Then Target = Range("A104")
Cells.Replace What:=("B104"), Replacement:=("A104"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B105") Then Target = Range("A105")
Cells.Replace What:=("B105"), Replacement:=("A105"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B106") Then Target = Range("A106")
Cells.Replace What:=("B106"), Replacement:=("A106"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B107") Then Target = Range("A107")
Cells.Replace What:=("B107"), Replacement:=("A107"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B108") Then Target = Range("A108")
Cells.Replace What:=("B108"), Replacement:=("A108"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B109") Then Target = Range("A109")
Cells.Replace What:=("B109"), Replacement:=("A109"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B110") Then Target = Range("A110")
Cells.Replace What:=("B110"), Replacement:=("A110"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B111") Then Target = Range("A111")
Cells.Replace What:=("B111"), Replacement:=("A111"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B112") Then Target = Range("A112")
Cells.Replace What:=("B12"), Replacement:=("A112"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            If cell = Range("B113") Then Target = Range("A113")
Cells.Replace What:=("B113"), Replacement:=("A113"), LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            
         Next
            End If
If Not Intersect(Target, Range("BO4:BO42")) Is Nothing Then
    For Each cell In Target
        If Range("A83") = cell.Value Then
            cell.Interior.ColorIndex = xlNone
        ElseIf Range("A84") = cell.Value Then
            cell.Interior.ColorIndex = 53
        ElseIf Range("A85") = cell.Value Then
            cell.Interior.ColorIndex = 4
        ElseIf Range("A88") = cell.Value Then
            cell.Interior.ColorIndex = 34
        ElseIf Range("A87") = cell.Value Then
            cell.Interior.ColorIndex = 33
        ElseIf Range("A86") = cell.Value Then
            cell.Interior.ColorIndex = 39
        ElseIf Range("A89") = cell.Value Then
            cell.Interior.ColorIndex = 25
            cell.Font.ColorIndex = 2
        ElseIf Range("A90") = cell.Value Then
            cell.Interior.ColorIndex = 10
        ElseIf Range("A99") = cell.Value Then
            cell.Interior.ColorIndex = 5
            cell.Font.ColorIndex = 2
        ElseIf Range("A92") = cell.Value Then
            cell.Interior.ColorIndex = 7
        ElseIf Range("A93") = cell.Value Then
            cell.Interior.ColorIndex = 12
        ElseIf Range("A94") = cell.Value Then
            cell.Interior.ColorIndex = 56
            cell.Font.ColorIndex = 2
        ElseIf Range("A95") = cell.Value Then
            cell.Interior.ColorIndex = 8
        ElseIf Range("A96") = cell.Value Then
            cell.Interior.ColorIndex = 6
        ElseIf Range("A97") = cell.Value Then
            cell.Interior.ColorIndex = 9
            cell.Font.ColorIndex = 2
        ElseIf Range("A98") = cell.Value Then
            cell.Interior.ColorIndex = 1
            cell.Font.ColorIndex = 3
        ElseIf Range("A91") = cell.Value Then
            cell.Interior.ColorIndex = 43
        ElseIf Range("A100") = cell.Value Then
            cell.Interior.ColorIndex = 50
        ElseIf Range("A101") = cell.Value Then
            cell.Interior.ColorIndex = 38
        ElseIf Range("A102") = cell.Value Then
            cell.Interior.ColorIndex = 35
        ElseIf Range("A103") = cell.Value Then
            cell.Interior.ColorIndex = 40
        ElseIf Range("A104") = cell.Value Then
            cell.Interior.ColorIndex = 44
        ElseIf Range("A105") = cell.Value Then
            cell.Interior.ColorIndex = 14
        ElseIf Range("A106") = cell.Value Then
            cell.Interior.ColorIndex = 17
        ElseIf Range("A107") = cell.Value Then
            cell.Interior.ColorIndex = 18
        ElseIf Range("A108") = cell.Value Then
            cell.Interior.ColorIndex = 19
        ElseIf Range("A109") = cell.Value Then
            cell.Interior.ColorIndex = 22
        ElseIf Range("A110") = cell.Value Then
            cell.Interior.ColorIndex = 24
        ElseIf Range("A111") = cell.Value Then
            cell.Interior.ColorIndex = 36
        ElseIf Range("A112") = cell.Value Then
            cell.Interior.ColorIndex = 46
        ElseIf Range("A113") = cell.Value Then
            cell.Interior.ColorIndex = 47
        
        End If
    Next
        End If

Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculate


End Sub
J'aimerai pouvoir mettre ca dans un bouton qui serais sur chaque feuille. On peut donc mettre le code sur chaque feuille et pas forcement sur le workbook.

Le but étant d'accélérer la saisie dans le fichier
 
Dernière modification par un modérateur:
Vote positif 0 Vote négatif
Re : Comment mettre une macro existante dans un bouton

re 🙂

Ca serait mieux avec un extrait du fichier, ceci dit pourrais-tu éditer ton post en utilisant la balise de codes? (le signe # juste aui dessus de la fenêtre de message) par ce que là c'est illisible.

bonne soirée à toi 🙂

mth
 
Vote positif 0 Vote négatif
Rejoins nos membres "Supporter XLD" et booste ton expérience Excel !
- Navigue sans publicité
- Accède à Cléa, notre assistante IA experte Excel... et pas que...
- Profite de fonctionnalités exclusives
Ton soutien permet à Excel Downloads de rester 100% gratuit et de continuer à rassembler les passionnés d'Excel.
Je deviens Supporter XLD
Assurez vous de marquer un message comme solution pour une meilleure transparence.

Discussions similaires

M
  • Question Question
Microsoft 365 automatiser un bouton "efface cellule"
Réponses
5
Affichages
321
matlatarte
M
A
  • Question Question
XL pour MAC Problème avec une macro dupliquant une feuille contenant une macro
Réponses
8
Affichages
389
AlineJ
A
M
  • Résolu(e)
Microsoft 365 $ Macro pour copier un exemple et$.....
Réponses
9
Affichages
522
sousou
sousou
treza88
  • Résolu(e)
XL 2021 Temps réel d'éxécution macro
Réponses
3
Affichages
262
treza88
treza88
D
  • Résolu(e)
Microsoft 365 chercher une cellule de couleur rouge dans une colonne
Réponses
4
Affichages
439
sylvanu
sylvanu
jeff1494
  • Question Question
XL 2021 Détecter un changement dans un des contrôles de formulaire et mettre à jour un TS correspondant.
2
Réponses
15
Affichages
716
jeff1494
jeff1494
P
  • Résolu(e)
XL 2021 Comment détecter la modification MANUELLE d'une cellule dans une feuille ?
Réponses
3
Affichages
340
PRy
P
C
  • Question Question
XL 2021 Mettre en couleur un texte dans listbox
Réponses
9
Affichages
836
ChTi160
ChTi160
S
  • Question Question
Macro associée à un bouton
Réponses
12
Affichages
581
Santino007
S
P
  • Question Question
Microsoft 365 VBA copier-coller selon conditions
Réponses
4
Affichages
325
vgendron
vgendron
treza88
  • Question Question
XL 2021 Copier coller qui ajoute une référence absolu Toute seule
2
Réponses
24
Affichages
1 K
TooFatBoy
TooFatBoy
Z
  • Résolu(e)
Microsoft 365 Mise en forme de QRCodes dans un model
Réponses
6
Affichages
433
Zaadox
Z
R
  • Question Question
Microsoft 365 VBA Excel PowerPoint
Réponses
5
Affichages
551
Rem6610
R
G
  • Résolu(e)
XL 2010 Mettre une textbox en négatif.
Réponses
6
Affichages
516
Phil69970
Phil69970
R
  • Question Question
Microsoft 365 Macro masquer/Afficher des lignes avec un bouton
Réponses
8
Affichages
719
Staple1600
Staple1600
L
  • Question Question
XL 2021 Macro qui détecte un bouton (formulaire)
Réponses
4
Affichages
538
Laetitia.B
L
E
  • Question Question
XL 2016 Liste déroulante "pré-chargée"
Réponses
10
Affichages
544
sylvanu
sylvanu
P
  • Question Question
XL 2019 Macro VBA appliquer un filtre lors d'une importation de données
Réponses
1
Affichages
575
fanch55
fanch55
A
  • Question Question
XL 2021 Macro copier dans un fichier Excel sur Sharepoint
Réponses
1
Affichages
471
vgendron
vgendron
patricktoulon
Comment utiliser application.ontime pour lancer une procédure qui n'est pas dans un module standard
Réponses
8
Affichages
780
Dranreb
Dranreb
Retour