change color and protect rows

[Guest]

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

 

[Frank Kabel]

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

 

[Guest]

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,

 

[Dave Peterson]

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?

 

[Myrna Larson]

c.Interior.Pattern = xlPatternGray50

Change that to

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

 

[Guest]

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]

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

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,

 

[Guest]

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]

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

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,

 

[Guest]

Perfect! Thanks!

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