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