Hot Fox: RE: ]] XL-mania [[ sederhanakan select case

domingo, 30 de agosto de 2009

RE: ]] XL-mania [[ sederhanakan select case

Dear Mas Sandy,

Saya coba bantu :

'======================================================

Sub Mark_South()

For Each c In Range("D10:D300").Cells
Select Case Left(c.Value, 4)
Case "TN-A", "TN-B", "TN-D", "TN-E", "TN-F", "TN-G", "TN-H", "TN-J", "TN-R", "TN-S", "TN-T", "TN-X"
    With c
        .Interior.Color = RGB(0, 0, 255)
        .Font.Color = RGB(255, 255, 255)
        .Font.Bold = True
    End With

End Select
Next c
End Sub

'======================================================

Kalau stringnya selalu diawali dengan string "TN-" maka bisa disederhanakan lagi menjadi:

'======================================================

Sub Mark_South()

For Each c In Range("D10:D300").Cells
Select Case Mid(c.Value, 4,1)
Case "A", "B", "D", "E", "F", "G", "H", "J", "R", "S", "T", "X"
    With c
        .Interior.Color = RGB(0, 0, 255)
        .Font.Color = RGB(255, 255, 255)
        .Font.Bold = True
    End With

End Select
Next c
End Sub

'======================================================

Salam Sukses,

Marly Lumenta

From: XL-mania@yahoogroups.com [mailto:XL-mania@yahoogroups.com] On Behalf Of Sandy Warsito
Sent: Saturday, August 29, 2009 8:07 AM
To: XL-mania@yahoogroups.com
Subject: ]] XL-mania [[ sederhanakan select case

Dear Suhu-suhu Excel yang budiman,

Saya telah menulis code seperti di bawah dan sudah bekerja dengan baik, namun dengan listing seperti ini sepertinya akan kesulitan jika suatu saat nanti perlu mengedit case-case nya. Please help untuk menyederhanakannya sehingga mengedit case akan lebih mudah.

Makasih.

'======================================================

Sub Mark_South()

For Each c In Range("D10:D300").Cells
Select Case Left(c.Value, 4)
Case "TN-A"
    With c
        .Interior.Color = RGB(0, 0, 255)
        .Font.Color = RGB(255, 255, 255)
        .Font.Bold = True
    End With
Case "TN-B"
    With c
        .Interior.Color = RGB(0, 0, 255)
        .Font.Color = RGB(255, 255, 255)
        .Font.Bold = True
    End With
Case "TN-D"
    With c
        .Interior.Color = RGB(0, 0, 255)
        .Font.Color = RGB(255, 255, 255)
        .Font.Bold = True
    End With
Case "TN-E"
    With c
        .Interior.Color = RGB(0, 0, 255)
        .Font.Color = RGB(255, 255, 255)
        .Font.Bold = True
    End With
Case "TN-F"
    With c
        .Interior.Color = RGB(0, 0, 255)
        .Font.Color = RGB(255, 255, 255)
        .Font.Bold = True
End With
Case "TN-G"
    With c
        .Interior.Color = RGB(0, 0, 255)
        .Font.Color = RGB(255, 255, 255)
        .Font.Bold = True
    End With
Case "TN-H"
    With c
        .Interior.Color = RGB(0, 0, 255)
        .Font.Color = RGB(255, 255, 255)
        .Font.Bold = True
    End With
Case "TN-J"
    With c
        .Interior.Color = RGB(0, 0, 255)
        .Font.Color = RGB(255, 255, 255)
        .Font.Bold = True
    End With
Case "TN-R"
    With c
        .Interior.Color = RGB(0, 0, 255)
        .Font.Color = RGB(255, 255, 255)
        .Font.Bold = True
    End With
Case "TN-S"
    With c
        .Interior.Color = RGB(0, 0, 255)
        .Font.Color = RGB(255, 255, 255)
        .Font.Bold = True
    End With
Case "TN-T"
    With c
        .Interior.Color = RGB(0, 0, 255)
        .Font.Color = RGB(255, 255, 255)
        .Font.Bold = True
    End With
Case "TN-T"
    With c
        .Interior.Color = RGB(0, 0, 255)
        .Font.Color = RGB(255, 255, 255)
        .Font.Bold = True
    End With

Case "TN-X"
    With c
        .Interior.Color = RGB(0, 0, 255)
        .Font.Color = RGB(255, 255, 255)
        .Font.Bold = True
    End With

End Select
Next c
End Sub

'======================================================

__._,_.___

Messages in this topic (4) Reply (via web post) | Start a new topic

Messages

+-:: XL-mania ::::::::::::::::::::----------------------------------+
| Selamat menjalankan ibadah puasa...                               |
| http://www.facebook.com/group.php?gid=37671048001&ref=mf          |
+-------------------------------------------------------------------+
| DILARANG : MLM, money game, OOT, iklan tanpa izin, SARA, testing, |
| pembicaraan pribadi, one line message, melecehkan, tidak sopan. |
+-------------------------------------------------------------------+
| Buat subjek yang kreatif, jangan : "tanya", "help", "mohon bantu" |
| Usahakan besar attachment < 200 kb. Gunakan winzip jika perlu. |
+-------------------------------------------------------------------+
| Ajak teman-teman Anda bergabung dengan mengirim e-mail kosong ke |
| XL-mania-subscribe@yahoogroups.com atau kirimkan mereka file dari |
| http://groups.yahoo.com/group/XL-mania/files/Promotion/           |
+-------------------------------------------------------------------+
| Berikan testimoni di : http://www.friendster.com/xlmania atau... |
| http://www.xl-mania.com/2008/06/testimoni-xl-mania.html           |
+-------------------------------------------------------------------+
| Message lama ada di :                                             |
| http://groups.yahoo.com/group/XL-mania/messages [perlu yahoo id] |
| http://www.mail-archive.com/xl-mania@yahoogroups.com              |
+-------------------------------------------------------------------+

Change settings via the Web (Yahoo! ID required)
Change settings via email: Switch delivery to Daily Digest | Switch format to Traditional
Visit Your Group | Yahoo! Groups Terms of Use | Unsubscribe

__,_._,___

Nenhum comentário:

Postar um comentário

Hot Fox

domingo, 30 de agosto de 2009

RE: ]] XL-mania [[ sederhanakan select case

Nenhum comentário:

Arquivo do blog