Betul mr Kid
Herry Sutjipto - 081399320035
PT. KALIMANTAN KUTAI ENERGI
Wisma Barito Pacific, Tower B, Lt. 6,
Jl. Let. Jend. S. Parman Kav. 62-63,
Jakarta 11410
-----> Send from my iPhone 3GS powered by Telkomsel <-----
On 23 Apr 2011, at 22:32, "Mr. Kid" <mr.nm...@gmail.com> wrote:
> Yang ini kan ?
>
>
> On Fri, Apr 22, 2011 at 09:41, Mr. Kid <mr.nm...@gmail.com> wrote:
> Coba juga :
>
> Private Sub Workbook_SheetDeactivate(ByVal sh As Object)
> Dim r As Long
> If sh.Name = Sheet1.Name Then
>
> For r = 6 To 36
> If sh.Range("R" & r) > 0 Then
> sh.Tab.Color = 16711935
> Exit Sub
>
> End If
> Next r
> Else
> For r = 5 To 38
> If sh.Range("M" & r) > 0 Then
> If LenB(sh.Range("P" & r)) = 0 Then
> sh.Tab.Color = 65535
> Exit Sub
> End If
>
> End If
> Next r
> End If
> sh.Tab.ColorIndex = 35
> End Sub
>
> Kid.
>
>
> 2011/4/22 Herry Sutjipto <herry...@yahoo.com>
>
> Mr Kid & bu Guru STDEV(i),
>
> benar, ternayat itu tidak diperlukan
>
> Private Sub Workbook_SheetDeactivate(ByVal sh As Object)
> Dim r As Long
> sh.Tab.ColorIndex = 35
>
> If sh.Name = Sheets(1).Name Then
> For r = 6 To 36
> If sh.Range("R" & r) > 0 Then
> sh.Tab.Color = 16711935
>
> Exit For
> End If
> Next r
> Else
> For r = 5 To 38
> If sh.Range("M" & r) > 0 And IsEmpty(sh.Range("P" & r)) Then
> sh.Tab.Color = 65535
>
> Exit For
> End If
> Next r
> End If
> End Sub
>
> Regards
> Herry Sutjipto
> From: Herry Sutjipto <herry...@yahoo.com>
> To: "belajar-excel@yahoogroups.com" <belajar-excel@yahoogroups.com>
> Sent: Friday, April 22, 2011 8:57 AM
>
> Subject: Re: Fw: [belajar-excel] Memberi warna sheet-name
>
>
> Baik workbook nya saya lampirkan
>
> From: STDEV(i) <setiyowati.d...@gmail.com>
> To: belajar-excel@yahoogroups.com
> Sent: Friday, April 22, 2011 8:46 AM
> Subject: Re: Fw: [belajar-excel] Memberi warna sheet-name
>
>
> iyya ya... kenapa ada loop nya ya??
> lagi pula nilai counter (lSheet) sepanjang loop tidak pernah digunakan
>
> seharusna memang harus lihat keadaan workbook dulu ya, baru bertindak..
>
> kalau ndak salah ada perubahan woerkbook, sejak dikatakan bahwa
> di sheet lain perlu warna lain, dan workbook belum dilampirkan
>
> ( kita menunggu "bendanya" dulu deh..) rapi size-nya jangan lebih dari jatah
>
>
>
> On Fri, Apr 22, 2011 at 8:34 AM, Mr. Kid <mr.nm...@gmail.com> wrote:
> kenapa harus di loop sejumlah sheet yg ada di workbook?
>
> kid
>
>
> On Fri, Apr 22, 2011 at 08:13, STDEV(i) <setiyowati.d...@gmail.com> wrote:
>
> mungkin bisa diperngkas menjadi spt ini
> mohon maaf kalau salah, karena dibuatnya di notepad, tanpa dicoba di workbook
> (mencari nilai kosong di range R6:R38 atau M5:M38 tidak perlu pakai Looping)
>
> Private Sub Workbook_SheetDeactivate(ByVal sh As Object)
> Dim lSheet As Long
> sh.Tab.ColorIndex = 35
> With WorksheetFunction
> For lSheet = 1 To Sheets.Count
> If sh.Name = Sheets(1).Name Then
> If .CountIf(sh.Range("R6:R38"), ">0") > 0 _
> Then sh.Tab.Color = 16711935
> Else
> If .CountIf(sh.Range("M5:M8"), ">0") > 0 _
> Then sh.Tab.Color = 65535
> End If
> Next lSheet
> End With
> End Sub
>
>
>
>
>
>
>
>
>
