Hi -

I'd like a short procedure to loop through a set number of worksheets
(named 1 to 31), testing for the existence of ANY data in cell D3 (on
each sheet) ... if NO DATA exists, the tab color should be changed to
Red.

This procedure will be used on computers running XP and XL02 ...

Any help is greatly appreciated!

Thanks,
Ray

Give this macro a try...

Sub CheckCellD3()
Dim WS As Worksheet
For Each WS In Worksheets
If WS.Range("D3").Value = "" Then
WS.Tab.ColorIndex = 3
Else
WS.Tab.ColorIndex = xlNone
End If
Next
End Sub

--
Rick (MVP - Excel)


"Ray" <(E-Mail Removed)> wrote in message
news:f1b9262c-8134-490a-bf92-(E-Mail Removed)...
> Hi -
>
> I'd like a short procedure to loop through a set number of worksheets
> (named 1 to 31), testing for the existence of ANY data in cell D3 (on
> each sheet) ... if NO DATA exists, the tab color should be changed to
> Red.
>
> This procedure will be used on computers running XP and XL02 ...
>
> Any help is greatly appreciated!
>
> Thanks,
> Ray

On Wed, 29 Jul 2009 14:27:30 -0400, "Rick Rothstein"
<(E-Mail Removed)> wrote:

>Give this macro a try...
>
>Sub CheckCellD3()
> Dim WS As Worksheet
> For Each WS In Worksheets
> If WS.Range("D3").Value = "" Then
> WS.Tab.ColorIndex = 3
> Else
> WS.Tab.ColorIndex = xlNone
> End If
> Next
>End Sub

And if you have other sheets in the workbook besides sheets 1,2,3,..31
that you don't want to include in this check you may replace the

For Each WS in Worksheets

with

For i = 1 to 31
Set WS = Worksheets(""&i)

Hope this helps / Lars-Åke

Good point! Thanks for picking up on that.

--
Rick (MVP - Excel)


"Lars-Åke Aspelin" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> On Wed, 29 Jul 2009 14:27:30 -0400, "Rick Rothstein"
> <(E-Mail Removed)> wrote:
>
>>Give this macro a try...
>>
>>Sub CheckCellD3()
>> Dim WS As Worksheet
>> For Each WS In Worksheets
>> If WS.Range("D3").Value = "" Then
>> WS.Tab.ColorIndex = 3
>> Else
>> WS.Tab.ColorIndex = xlNone
>> End If
>> Next
>>End Sub
>
> And if you have other sheets in the workbook besides sheets 1,2,3,..31
> that you don't want to include in this check you may replace the
>
> For Each WS in Worksheets
>
> with
>
> For i = 1 to 31
> Set WS = Worksheets(""&i)
>
> Hope this helps / Lars-Åke
>
>

Rick & Lars-Åke -

Thanks very much ... worked perfectly! and I DID have more than just
the Sheets titled 1-31, so Lars-Åke's suggestion was definitely
useful!

//ray


On Jul 29, 2:52*pm, "Rick Rothstein"
<rick.newsNO.S...@NO.SPAMverizon.net> wrote:
> Good point! Thanks for picking up on that.
>
> --
> Rick (MVP - Excel)
>
> "Lars-Åke Aspelin" <lar...@REMOOOVE.telia.com> wrote in message
>
> news:(E-Mail Removed)...
>
> > On Wed, 29 Jul 2009 14:27:30 -0400, "Rick Rothstein"
> > <rick.newsNO.S...@NO.SPAMverizon.net> wrote:
>
> >>Give this macro a try...
>
> >>Sub CheckCellD3()
> >> *Dim WS As Worksheet
> >> *For Each WS In Worksheets
> >> * *If WS.Range("D3").Value = "" Then
> >> * * *WS.Tab.ColorIndex = 3
> >> * *Else
> >> * * *WS.Tab.ColorIndex = xlNone
> >> * *End If
> >> *Next
> >>End Sub
>
> > And if you have other sheets in the workbook besides sheets 1,2,3,..31
> > that you don't want to include in this check you may replace the
>
> > For Each WS in Worksheets
>
> > with
>
> > For i = 1 to 31
> > Set WS = Worksheets(""&i)
>
> > Hope this helps / Lars-Åke