Dear Mr Kid,
karena di sheet (2) dst seterusnya backgroud sheetname akan diberi warna, jika
memenuhi ketentuan sh.Range("M" & r) > 0 And IsEmpty(sh.Range("P" & r)) ; r = 5
to 38
Regards,
herry s
From: Mr. Kid <mr.nm...@gmail.com>
To: belajar-excel@yahoogroups.com
Sent: Friday, April 22, 2011 8:34 AM
Subject: Re: Fw: [belajar-excel] Memberi warna sheet-name
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
>
>
>
>
>
>
>
>
>2011/4/22 Herry Sutjipto <herry...@yahoo.com>
>
>
>>Dear Mr Kid,
>>
>>Terima kasih atas masukannya.
>>Saya jadi bisa mengembangkan macro dari bu guru STDEV(i) menjadi sbb:
>>
>>Private Sub Workbook_SheetDeactivate(ByVal sh As Object)
>> Dim r As Long
>> Dim lSheet As Long
>> sh.Tab.ColorIndex = 35
>> For lSheet = 1 To Sheets.Count
>> If sh.Name = Sheets(1).Name Then
>> Kolor = 16711935
>>
>> For r = 6 To 38
>> If sh.Range("R" & r) > 0 Then
>> sh.Tab.Color = Kolor
>> 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
>> Next lSheet
>>End Sub
>>
>>Regards
>>Herry Sutjipto
>>
>>
>>
