Hi, everyone! Please, I need your help!!!

I have a worksheet with dates on a1:a1096
The following code is working nicely and it changes
the appropriate cells to gray.
1) How can I extend the
"graying" to include the rows up to BQ?
2) And, also, lock those rows?
------------------------------
Private Sub ChangeColorToGray_Click()
Dim Y As Long
Worksheets(1).Unprotect
Y = (Year(Date))
With Worksheets(1).Range("a1:a1096")
Set c = .Find(Y, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Interior.Pattern = xlPatternGray50
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
Worksheets(1).Protect
End Sub
-------------------------------
As always, thanks in advance and your help is very appreciated!

Hi
change the statement
With Worksheets(1).Range("a1:a1096")
to
With Worksheets(1).Range("a1:BQ1096")

--
Regards
Frank Kabel
Frankfurt, Germany


Myriam wrote:
Hi, everyone! Please, I need your help!!!

I have a worksheet with dates on a1:a1096
The following code is working nicely and it changes
the appropriate cells to gray.
1) How can I extend the
"graying" to include the rows up to BQ?
2) And, also, lock those rows?
------------------------------
Private Sub ChangeColorToGray_Click()
Dim Y As Long
Worksheets(1).Unprotect
Y = (Year(Date))
With Worksheets(1).Range("a1:a1096")
Set c = .Find(Y, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Interior.Pattern = xlPatternGray50
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
Worksheets(1).Protect
End Sub
-------------------------------
As always, thanks in advance and your help is very appreciated!

Frank,
I wish it was that easy!
It only picks up column A where it is matching the date.
I need to extend the color to include 68 columns to the right? And lock
those cells.
How do I reference those columns?
Thanks,

"Frank Kabel" wrote:

Hi
change the statement
With Worksheets(1).Range("a1:a1096")
to
With Worksheets(1).Range("a1:BQ1096")

--
Regards
Frank Kabel
Frankfurt, Germany


Myriam wrote:
Hi, everyone! Please, I need your help!!!

I have a worksheet with dates on a1:a1096
The following code is working nicely and it changes
the appropriate cells to gray.
1) How can I extend the
"graying" to include the rows up to BQ?
2) And, also, lock those rows?
------------------------------
Private Sub ChangeColorToGray_Click()
Dim Y As Long
Worksheets(1).Unprotect
Y = (Year(Date))
With Worksheets(1).Range("a1:a1096")
Set c = .Find(Y, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Interior.Pattern = xlPatternGray50
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
Worksheets(1).Protect
End Sub
-------------------------------
As always, thanks in advance and your help is very appreciated!

On Thu, 14 Oct 2004 14:59:02 -0700, Myriam
wrote:

c.Interior.Pattern = xlPatternGray50

Change that to

c.Resize(1,68).Interior.Pattern = xlPatternGray50

maybe:

Private Sub ChangeColorToGray_Click()
Dim Y As Long
Worksheets(1).Unprotect
Y = (Year(Date))
With Worksheets(1).Range("a1:a1096")
Set c = .Find(Y, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Resize(1, 69).Interior.Pattern = xlPatternGray50
c.resize(1, 69).cells.locked = true
'entirerow??
'c.entirerow.cells.locked = true
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
Worksheets(1).Protect
End Sub

c.resize(1,69)
extends from A (where C is located) for 69 columns--all the way to BQ.

And did you really mean the whole row--or just A:BQ?



Myriam wrote:

Hi, everyone! Please, I need your help!!!

I have a worksheet with dates on a1:a1096
The following code is working nicely and it changes
the appropriate cells to gray.
1) How can I extend the
"graying" to include the rows up to BQ?
2) And, also, lock those rows?
------------------------------
Private Sub ChangeColorToGray_Click()
Dim Y As Long
Worksheets(1).Unprotect
Y = (Year(Date))
With Worksheets(1).Range("a1:a1096")
Set c = .Find(Y, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Interior.Pattern = xlPatternGray50
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
Worksheets(1).Protect
End Sub
-------------------------------
As always, thanks in advance and your help is very appreciated!

--

Dave Peterson

Works great! Thank you, Myrna and Dave.

And, yes it was up to BQ not the entirerow, but thanks for including the
entire row, it will come in handy soon : ) thanks.

One last question.
If I want Y to be = years "different than" Year(Date)
I assumed that I could write Y<Year(Date)
but I get an error. What would be the correct code?
Thanks,

"Dave Peterson" wrote:

maybe:

Private Sub ChangeColorToGray_Click()
Dim Y As Long
Worksheets(1).Unprotect
Y = (Year(Date))
With Worksheets(1).Range("a1:a1096")
Set c = .Find(Y, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Resize(1, 69).Interior.Pattern = xlPatternGray50
c.resize(1, 69).cells.locked = true
'entirerow??
'c.entirerow.cells.locked = true
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
Worksheets(1).Protect
End Sub

c.resize(1,69)
extends from A (where C is located) for 69 columns--all the way to BQ.

And did you really mean the whole row--or just A:BQ?



Myriam wrote:

Hi, everyone! Please, I need your help!!!

I have a worksheet with dates on a1:a1096
The following code is working nicely and it changes
the appropriate cells to gray.
1) How can I extend the
"graying" to include the rows up to BQ?
2) And, also, lock those rows?
------------------------------
Private Sub ChangeColorToGray_Click()
Dim Y As Long
Worksheets(1).Unprotect
Y = (Year(Date))
With Worksheets(1).Range("a1:a1096")
Set c = .Find(Y, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Interior.Pattern = xlPatternGray50
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
Worksheets(1).Protect
End Sub
-------------------------------
As always, thanks in advance and your help is very appreciated!

--

Dave Peterson