Comment mettre une macro existante dans un bouton

H

highlander

XLDnaute Nouveau
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
mth

mth

XLDnaute Barbatruc
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
H

highlander

XLDnaute Nouveau
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 édition:
Vote positif 0 Vote négatif
mth

mth

XLDnaute Barbatruc
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

Discussions similaires

P
  • Question
XL 2016 Affecter à un bouton plusieurs possibilité
Réponses
3
Affichages
344
Eric KERGRESSE
E
T
  • Question
Microsoft 365 Copier et incrémenter une plage de données dans un autre onglet
Réponses
7
Affichages
452
tonino06200
T
C
  • Résolu(e)
Microsoft 365 Mise en forme d'une ligne d'après listes déroulantes + clic sur un bouton
Réponses
2
Affichages
342
casualexcel
C
T
  • Résolu(e)
XL 2021 de mettre une couleur sur chaque mot commencant par a ou b ou c
Réponses
4
Affichages
281
target
T
F
  • Résolu(e)
Microsoft 365 Publipostage créer un nombre d'étiquettes en fonction d'une colonne Excel
Réponses
7
Affichages
558
Cousinhub
Cousinhub
D
  • Question
Microsoft 365 Macro VB pour coller une valeur dans plusieurs cellules
Réponses
2
Affichages
230
did4233
D
J
  • Question
Microsoft 365 Probleme de changement de couleur d'un bouton
2
Réponses
25
Affichages
906
patricktoulon
patricktoulon
S
  • Résolu(e)
XL 2019 Couper coller une ligne d'une feuille1 vers feuille2 grâce à un bouton ou case à cocher
Réponses
6
Affichages
636
sylvanu
sylvanu
F
  • Résolu(e)
XL 2021 Figer une sélection de cellules quand on se déplace dans la feuille
Réponses
4
Affichages
280
fanou06
F
L
  • Question
XL 2013 Liaisons Word / Excel : quelle solution retenir et comment la mettre en œuvre
2 3 4
Réponses
50
Affichages
3 K
LMOAL
L

Membres actuellement en ligne

22 membres et 321 invités.

Partager cette page

Tutos vidéos

Power Query : Ajouter des requêtes (Excel et Power BI)
Créez votre première fonction LAMBDA dans Excel en 3 étapes!
LAMBDA, l’arme secrète de Microsoft pour Excel !
Office Insider - Découvrez les nouveautés Excel avant tout le monde

Statistiques des forums

Discussions
312 315
Messages
2 087 168
Membres
103 489
dernier inscrit
CACCA