Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change color of rows every time value in first column changes | Excel Worksheet Functions | |||
Change Color on rows, after an IF Funtion is answered | Excel Worksheet Functions | |||
How do I change color of cells, columns and rows in Excel? | New Users to Excel | |||
To allow edit of particular rows and protect other rows from editi | Excel Discussion (Misc queries) | |||
How do I change the color of the Rows and Coumns headings | Setting up and Configuration of Excel |