change color and protect rows
 

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!

change color and protect rows
 

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!

change color and protect rows
 

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!

change color and protect rows
 

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

change color and protect rows
 

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

change color and protect rows
 

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

change color and protect rows
 

I ran it and I got the whole range gray.
It did not pick up the 'do nothing
I have Column A custom formatted yyy
Should I do something different?
In fact, it would be better if it could pick the
year from a normal date 12/31/04..
Thanks,



"Dave Peterson" wrote:

I think you'd have to take another approach.

You're doing a .Find in your code. And you're looking for the Year.

If you want to look for a year not equal to the current year, you'd either have
to go through all the possible years (finding each and doing your stuff) or just
loop through the cells.

kind of:

Option Explicit

Private Sub ChangeColorToGray_Click()
Dim Y As Long
Dim myCell As Range
Dim myRng As Range

Set myRng = Worksheets(1).Range("a1:a1096")
Worksheets(1).Unprotect

Y = (Year(Date))

For Each myCell In myRng.Cells
If myCell.Value = Y Then
'do nothing
Else
myCell.Resize(1, 69).Interior.Pattern = xlPatternGray50
myCell.Resize(1, 69).Cells.Locked = True
'entirerow??
'mycell.entirerow.cells.locked = true
End If
Next myCell
Worksheets(1).Protect
End Sub

I'm assuming that the whole cell contained the year. (just 2004--not
12/31/2004).



Myriam wrote:

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



--

Dave Peterson

change color and protect rows
 

Ah, you don't just have the year in the cell--you have a real date and it's
formatted to just show the year.

If that's right:
change:
If mycell.value = y then
to:
If year(myCell.Value) = Y Then





Myriam wrote:

I ran it and I got the whole range gray.
It did not pick up the 'do nothing
I have Column A custom formatted yyy
Should I do something different?
In fact, it would be better if it could pick the
year from a normal date 12/31/04..
Thanks,

"Dave Peterson" wrote:

I think you'd have to take another approach.

You're doing a .Find in your code. And you're looking for the Year.

If you want to look for a year not equal to the current year, you'd either have
to go through all the possible years (finding each and doing your stuff) or just
loop through the cells.

kind of:

Option Explicit

Private Sub ChangeColorToGray_Click()
Dim Y As Long
Dim myCell As Range
Dim myRng As Range

Set myRng = Worksheets(1).Range("a1:a1096")
Worksheets(1).Unprotect

Y = (Year(Date))

For Each myCell In myRng.Cells
If myCell.Value = Y Then
'do nothing
Else
myCell.Resize(1, 69).Interior.Pattern = xlPatternGray50
myCell.Resize(1, 69).Cells.Locked = True
'entirerow??
'mycell.entirerow.cells.locked = true
End If
Next myCell
Worksheets(1).Protect
End Sub

I'm assuming that the whole cell contained the year. (just 2004--not
12/31/2004).



Myriam wrote:

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



--

Dave Peterson



--

Dave Peterson

change color and protect rows
 

Perfect! Thanks!

"Dave Peterson" wrote:

Ah, you don't just have the year in the cell--you have a real date and it's
formatted to just show the year.

If that's right:
change:
If mycell.value = y then
to:
If year(myCell.Value) = Y Then





Myriam wrote:

I ran it and I got the whole range gray.
It did not pick up the 'do nothing
I have Column A custom formatted yyy
Should I do something different?
In fact, it would be better if it could pick the
year from a normal date 12/31/04..
Thanks,

"Dave Peterson" wrote:

I think you'd have to take another approach.

You're doing a .Find in your code. And you're looking for the Year.

If you want to look for a year not equal to the current year, you'd either have
to go through all the possible years (finding each and doing your stuff) or just
loop through the cells.

kind of:

Option Explicit

Private Sub ChangeColorToGray_Click()
Dim Y As Long
Dim myCell As Range
Dim myRng As Range

Set myRng = Worksheets(1).Range("a1:a1096")
Worksheets(1).Unprotect

Y = (Year(Date))

For Each myCell In myRng.Cells
If myCell.Value = Y Then
'do nothing
Else
myCell.Resize(1, 69).Interior.Pattern = xlPatternGray50
myCell.Resize(1, 69).Cells.Locked = True
'entirerow??
'mycell.entirerow.cells.locked = true
End If
Next myCell
Worksheets(1).Protect
End Sub

I'm assuming that the whole cell contained the year. (just 2004--not
12/31/2004).



Myriam wrote:

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



--

Dave Peterson



--

Dave Peterson