Code verkürzen
07.10.2008 14:52:00
Frank
Folgenden Code benutze ich um meine Optik aufzupeppen!
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
CommandButton001.Caption = Range("AQ3").Text
If CommandButton001.Caption = Range("AN1") Then
CommandButton001.BackColor = RGB(0, 0, 255)
Else
CommandButton001.BackColor = RGB(225, 225, 225)
End If
CommandButton002.Caption = Range("AQ4").Text
If CommandButton002.Caption = Range("AN1") Then
CommandButton002.BackColor = RGB(0, 0, 255)
Else
CommandButton002.BackColor = RGB(225, 225, 225)
End If
CommandButton003.Caption = Range("AQ5").Text
If CommandButton003.Caption = Range("AN1") Then
CommandButton003.BackColor = RGB(0, 0, 255)
Else
CommandButton003.BackColor = RGB(225, 225, 225)
End If
CommandButton004.Caption = Range("AQ6").Text
If CommandButton004.Caption = Range("AN1") Then
CommandButton004.BackColor = RGB(0, 0, 255)
Else
CommandButton004.BackColor = RGB(225, 225, 225)
End If
CommandButton005.Caption = Range("AQ7").Text
If CommandButton005.Caption = Range("AN1") Then
CommandButton005.BackColor = RGB(0, 0, 255)
Else
CommandButton005.BackColor = RGB(225, 225, 225)
End If
CommandButton006.Caption = Range("AQ8").Text
If CommandButton006.Caption = Range("AN1") Then
CommandButton006.BackColor = RGB(0, 0, 255)
Else
CommandButton006.BackColor = RGB(225, 225, 225)
End If
CommandButton007.Caption = Range("AQ9").Text
If CommandButton007.Caption = Range("AN1") Then
CommandButton007.BackColor = RGB(0, 0, 255)
Else
CommandButton007.BackColor = RGB(225, 225, 225)
End If
CommandButton008.Caption = Range("AQ10").Text
If CommandButton008.Caption = Range("AN1") Then
CommandButton008.BackColor = RGB(0, 0, 255)
Else
CommandButton008.BackColor = RGB(225, 225, 225)
End If
CommandButton009.Caption = Range("AQ11").Text
If CommandButton009.Caption = Range("AN1") Then
CommandButton009.BackColor = RGB(0, 0, 255)
Else
CommandButton009.BackColor = RGB(225, 225, 225)
End If
CommandButton010.Caption = Range("AQ12").Text
If CommandButton010.Caption = Range("AN1") Then
CommandButton010.BackColor = RGB(0, 0, 255)
Else
CommandButton010.BackColor = RGB(225, 225, 225)
End If
CommandButton011.Caption = Range("AQ13").Text
If CommandButton011.Caption = Range("AN1") Then
CommandButton011.BackColor = RGB(0, 0, 255)
Else
CommandButton011.BackColor = RGB(225, 225, 225)
End If
CommandButton012.Caption = Range("AQ14").Text
If CommandButton012.Caption = Range("AN1") Then
CommandButton012.BackColor = RGB(0, 0, 255)
Else
CommandButton012.BackColor = RGB(225, 225, 225)
End If
CommandButton013.Caption = Range("AQ15").Text
If CommandButton013.Caption = Range("AN1") Then
CommandButton013.BackColor = RGB(0, 0, 255)
Else
CommandButton013.BackColor = RGB(225, 225, 225)
End If
CommandButton014.Caption = Range("AQ16").Text
If CommandButton014.Caption = Range("AN1") Then
CommandButton014.BackColor = RGB(0, 0, 255)
Else
CommandButton014.BackColor = RGB(225, 225, 225)
End If
CommandButton015.Caption = Range("AQ17").Text
If CommandButton015.Caption = Range("AN1") Then
CommandButton015.BackColor = RGB(0, 0, 255)
Else
CommandButton015.BackColor = RGB(225, 225, 225)
End If
CommandButton016.Caption = Range("AQ18").Text
If CommandButton016.Caption = Range("AN1") Then
CommandButton016.BackColor = RGB(0, 0, 255)
Else
CommandButton016.BackColor = RGB(225, 225, 225)
End If
CommandButton017.Caption = Range("AQ19").Text
If CommandButton017.Caption = Range("AN1") Then
CommandButton017.BackColor = RGB(0, 0, 255)
Else
CommandButton017.BackColor = RGB(225, 225, 225)
End If
CommandButton018.Caption = Range("AQ20").Text
If CommandButton018.Caption = Range("AN1") Then
CommandButton018.BackColor = RGB(0, 0, 255)
Else
CommandButton018.BackColor = RGB(225, 225, 225)
End If
CommandButton019.Caption = Range("AQ21").Text
If CommandButton019.Caption = Range("AN1") Then
CommandButton019.BackColor = RGB(0, 0, 255)
Else
CommandButton019.BackColor = RGB(225, 225, 225)
End If
CommandButton020.Caption = Range("AQ22").Text
If CommandButton020.Caption = Range("AN1") Then
CommandButton020.BackColor = RGB(0, 0, 255)
Else
CommandButton020.BackColor = RGB(225, 225, 225)
End If
End Sub
Danach folgt CommandButton021 und Range beginnt wieder bei "AQ3" und das ganze soll sich dann fortlaufend bis zum CommandButton400 ziehen. Jedoch sagt mir eine Fehlermeldung "Prozedur zu lang". Wie kann ich die ganze Geschichte verkürzen und damit vereinfachen?
Wer kann helfenb? Vorab Danke!!!
Gruß Frank H.