Makro ile koşullu biçimlendirme...

[Jeeday]

EĞER U2 HÜCRESİNDEKİ DEĞER A1 HÜCRESİNE EŞİT İSE BÜTÜN GRİ OLAN HÜCRELER (U4:AK13, U14:AG14, U15:AK15) AÇIK YEŞİL, KOYU MAVİ (U2:AK2) OLAN HÜCRELER DE KOYU YEŞİL OLSUN…

kod ile yapılmasını istiyorum...

eke bakınca daha net anlaşılır sanırım

 

[Korhan Ayhan]

Merhaba,

Ben hücre seçimi olayına kodu yazdım. Siz dilerseniz farklı bir sayfa olayına yazabilirsiniz.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Range("U2") = Range("A1") Then
        Range("U4:AK13, U14:AG14, U15:AK15").Interior.ColorIndex = 50
        Range("U2:AK2").Interior.ColorIndex = 51
    Else
        Range("U4:AK13, U14:AG14, U15:AK15").Interior.ColorIndex = 15
        Range("U2:AK2").Interior.ColorIndex = 41
    End If
End Sub

 

[Jeeday]

ThisWorkbook bölümüne bu kodu yazdım verdiğiniz bilgi doğrultusunda... Bütün sayfalarda sorunsuz çalışıyor... fakat dosyama ne yazarsam yazayım "undo" yapamıyorum....

acaba nasıl düzeltebiliriz?


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Range("U2") = Range("A1") Then
Range("U4:AK13, U14:AG14, U15:AK15").Interior.Color = 52377
Range("U4:AK15").Font.Color = 16777215
Range("U2:AK3").Interior.Color = 26112
Range("AH14").Interior.Color = 26112
Else
Range("U4:AK13, U14:AG14, U15:AK15").Interior.Color = 14211288
Range("U2:AK2").Interior.Color = 12611584
Range("U3:AK3").Interior.Color = 14922893
Range("AH14").Interior.Color = 12611584
Range("U4:AK13, U14:AG14, U15:AK15").Font.Color = 0
End If
If Range("AL2") = Range("A1") Then
Range("AL4:BB13, AL14:AX14, AL15:BB15").Interior.Color = 52377
Range("AL4:BB15").Font.Color = 16777215
Range("AL2:BB3").Interior.Color = 26112
Range("AY14").Interior.Color = 26112
Else
Range("AL4:BB13, AL14:AX14, AL15:BB15").Interior.Color = 14211288
Range("AL2:BB2").Interior.Color = 12611584
Range("AL3:BB3").Interior.Color = 14922893
Range("AY14").Interior.Color = 12611584
Range("AL4:BB13, AL14:AX14, AL15:BB15").Font.Color = 0
End If
If Range("BC2") = Range("A1") Then
Range("BC4:BS13, BC14:BO14, BC15:BS15").Interior.Color = 52377
Range("BC4:BS15").Font.Color = 16777215
Range("BC2:BS3").Interior.Color = 26112
Range("BP14").Interior.Color = 26112
Else
Range("BC4:BS13, BC14:BO14, BC15:BS15").Interior.Color = 14211288
Range("BC2:BS2").Interior.Color = 12611584
Range("BC3:BS3").Interior.Color = 14922893
Range("BP14").Interior.Color = 12611584
Range("BC4:BS13, BC14:BO14, BC15:BS15").Font.Color = 0
End If
If Range("U16") = Range("A1") Then
Range("U18:AK27, U28:AG28, U29:AK29").Interior.Color = 52377
Range("U18:AK29").Font.Color = 16777215
Range("U16:AK17").Interior.Color = 26112
Range("AH28").Interior.Color = 26112
Else
Range("U18:AK27, U28:AG28, U29:AK29").Interior.Color = 14211288
Range("U16:AK16").Interior.Color = 12611584
Range("U17:AK17").Interior.Color = 14922893
Range("AH28").Interior.Color = 12611584
Range("U18:AK27, U28:AG28, U29:AK29").Font.Color = 0
End If
If Range("AL16") = Range("A1") Then
Range("AL18:BB27, AL28:AX28, AL29:BB29").Interior.Color = 52377
Range("AL18:BB29").Font.Color = 16777215
Range("AL16:BB17").Interior.Color = 26112
Range("AY28").Interior.Color = 26112
Else
Range("AL18:BB27, AL28:AX28, AL29:BB29").Interior.Color = 14211288
Range("AL16:BB16").Interior.Color = 12611584
Range("AL17:BB17").Interior.Color = 14922893
Range("AY28").Interior.Color = 12611584
Range("AL18:BB27, AL28:AX28, AL29:BB29").Font.Color = 0
End If
If Range("BC16") = Range("A1") Then
Range("BC18:BS27, BC28:BO28, BC29:BS29").Interior.Color = 52377
Range("BC18:BS29").Font.Color = 16777215
Range("BC16:BS17").Interior.Color = 26112
Range("BP28").Interior.Color = 26112
Else
Range("BC18:BS27, BC28:BO28, BC29:BS29").Interior.Color = 14211288
Range("BC16:BS16").Interior.Color = 12611584
Range("BC17:BS17").Interior.Color = 14922893
Range("BP28").Interior.Color = 12611584
Range("BC18:BS27, BC28:BO28, BC29:BS29").Font.Color = 0
End If
If Range("BC30") = Range("A1") Then
Range("BC32:BS41, BC42:BO42, BC43:BS43").Interior.Color = 52377
Range("BC32:BS43").Font.Color = 16777215
Range("BC30:BS31").Interior.Color = 26112
Range("BP42").Interior.Color = 26112
Else
Range("BC32:BS41, BC42:BO42, BC43:BS43").Interior.Color = 14211288
Range("BC30:BS30").Interior.Color = 12611584
Range("BC31:BS31").Interior.Color = 14922893
Range("BP42").Interior.Color = 12611584
Range("BC32:BS41, BC42:BO42, BC43:BS43").Font.Color = 0
End If
End Sub

 

[Korhan Ayhan]

Maalesef makro kullanılan dosyalarda "GERİ AL" komutu çalışmaz.