C
Cindy
Is there any function for me to set up color change for the row that I am
working on, so, I won't mistype row for my worksheet?
working on, so, I won't mistype row for my worksheet?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
G
Gord Dibben
There are methods using sheetchange events but the easiest method is to use
Chip Pearson's RowLiner add-in.
http://www.cpearson.com/excel/RowLiner.htm
Just a note...........Rowliner won't work on protected sheets.
If your sheet is protected you need event code pasted into the sheet module.
Right-click on the sheet tab and "View Code" Paste into that module.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static OldCell As Range
If Application.CutCopyMode = 0 Then
ActiveSheet.Unprotect Password:="justme"
If Not OldCell Is Nothing Then
With OldCell.EntireRow
.Interior.ColorIndex = xlColorIndexNone
.Borders.LineStyle = xlLineStyleNone
End With
End If
Set OldCell = Target
With OldCell.EntireRow
.Interior.ColorIndex = 6
.EntireRow.Borders.LineStyle = xlContinuous
End With
Else
If OldCell Is Nothing Then
Set OldCell = Target
Else
Set OldCell = Union(OldCell, Target)
End If
End If
ActiveSheet.Protect Password:="justme"
End Sub
Will color the active row yellow.
Note: will wipe out existing background color of activecell cell unless BG
color is due to CF
Gord Dibben MS Excel MVP
Chip Pearson's RowLiner add-in.
http://www.cpearson.com/excel/RowLiner.htm
Just a note...........Rowliner won't work on protected sheets.
If your sheet is protected you need event code pasted into the sheet module.
Right-click on the sheet tab and "View Code" Paste into that module.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static OldCell As Range
If Application.CutCopyMode = 0 Then
ActiveSheet.Unprotect Password:="justme"
If Not OldCell Is Nothing Then
With OldCell.EntireRow
.Interior.ColorIndex = xlColorIndexNone
.Borders.LineStyle = xlLineStyleNone
End With
End If
Set OldCell = Target
With OldCell.EntireRow
.Interior.ColorIndex = 6
.EntireRow.Borders.LineStyle = xlContinuous
End With
Else
If OldCell Is Nothing Then
Set OldCell = Target
Else
Set OldCell = Union(OldCell, Target)
End If
End If
ActiveSheet.Protect Password:="justme"
End Sub
Will color the active row yellow.
Note: will wipe out existing background color of activecell cell unless BG
color is due to CF
Gord Dibben MS Excel MVP
L
L. Howard Kittle
And just for fun, if you do not want to do all 256 columns of the row you
selected, you could give this a try:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Data As Range
Dim i As Integer
Dim j As Integer
Dim k As Integer
i = 2
j = 8
k = ActiveCell.Column()
Set Data = Range("B8:K22")
Data.Interior.ColorIndex = xlNone
If ActiveCell.Row < 8 Or ActiveCell.Row > 22 Or _
ActiveCell.Column < 2 Or ActiveCell.Column > 11 Then
Exit Sub
End If
ActiveCell.Offset(0, -(k - i)). _
Resize(1, 10).Interior.ColorIndex = 35 'try 26 it's is pretty wild!!!
End Sub
Where this will highlight the row of the cell selected in the named range
Data which is Range(B8:K22). Of course the code would need to be tweeked to
suit your sheet operational range. If you have other parts of the sheet
with colored cells OUTSIDE the Data range then these will remain intact.
Chips code does not allow this but I believe there is code out there that
will retain the current formatting and still highlight your selected row and
return all the formatting when you reselect a cell. That is above my
paygrade and Chip and other MVP's can help you if that is needed.
Now with Chip Pearson's code if you select more that one cell in the column
then it will highlight all rows selected. That could be "gooder" if you
wanted several rows highlighted as your data may relate to stuff above and
below your primary row.
HTH
Regards
Howard
selected, you could give this a try:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Data As Range
Dim i As Integer
Dim j As Integer
Dim k As Integer
i = 2
j = 8
k = ActiveCell.Column()
Set Data = Range("B8:K22")
Data.Interior.ColorIndex = xlNone
If ActiveCell.Row < 8 Or ActiveCell.Row > 22 Or _
ActiveCell.Column < 2 Or ActiveCell.Column > 11 Then
Exit Sub
End If
ActiveCell.Offset(0, -(k - i)). _
Resize(1, 10).Interior.ColorIndex = 35 'try 26 it's is pretty wild!!!
End Sub
Where this will highlight the row of the cell selected in the named range
Data which is Range(B8:K22). Of course the code would need to be tweeked to
suit your sheet operational range. If you have other parts of the sheet
with colored cells OUTSIDE the Data range then these will remain intact.
Chips code does not allow this but I believe there is code out there that
will retain the current formatting and still highlight your selected row and
return all the formatting when you reselect a cell. That is above my
paygrade and Chip and other MVP's can help you if that is needed.
Now with Chip Pearson's code if you select more that one cell in the column
then it will highlight all rows selected. That could be "gooder" if you
wanted several rows highlighted as your data may relate to stuff above and
below your primary row.
HTH
Regards
Howard
L
L. Howard Kittle
Whoa, I have confused Chips code with that of Gords! And I believe most of
what I said about Chips code is false.
Would be fair to disregard my post but you may try the code I offered for a
limited range of row highlighting.
Sorry, really got ahead of myself here folks.
Regards,
Howard
what I said about Chips code is false.
Would be fair to disregard my post but you may try the code I offered for a
limited range of row highlighting.
Sorry, really got ahead of myself here folks.
Regards,
Howard
E
Eurika Stemmet
Hi what does the CF mean in the following sentence:
"will wipe out existing background color of active cell unless BG color is due to CF" Is it Conditional Formating?
I used the event code, but then al my background colours and lines changes to white. I do I stop that from happening?
I created a program for our school for the marks of each term and that is linked to a final report. The teachers are going to enter their marks. I protected my worksheet so that they wont be able to delete my formula's. Not everyone is on the same level with computer skills and highlighting the row will help them a lot when they enter the marks.
Thanks
Gord Dibben wrote:
There are methods using sheetchange events but the easiest method is to
05-Feb-10
There are methods using sheetchange events but the easiest method is to us
Chip Pearson's RowLiner add-in
http://www.cpearson.com/excel/RowLiner.ht
Just a note...........Rowliner will not work on protected sheets
If your sheet is protected you need event code pasted into the sheet module
Right-click on the sheet tab and "View Code" Paste into that module
Private Sub Worksheet_SelectionChange(ByVal Target As Range
Static OldCell As Rang
If Application.CutCopyMode = 0 The
ActiveSheet.Unprotect Password:="justme
If Not OldCell Is Nothing The
With OldCell.EntireRo
..Interior.ColorIndex = xlColorIndexNon
..Borders.LineStyle = xlLineStyleNon
End Wit
End I
Set OldCell = Targe
With OldCell.EntireRo
..Interior.ColorIndex =
..EntireRow.Borders.LineStyle = xlContinuou
End Wit
Els
If OldCell Is Nothing The
Set OldCell = Targe
Els
Set OldCell = Union(OldCell, Target
End I
End I
ActiveSheet.Protect Password:="justme
End Su
Will color the active row yellow
Note: will wipe out existing background color of activecell cell unless B
color is due to C
Gord Dibben MS Excel MV
wrote:
Previous Posts In This Thread:
automaticely highlight the row that I am working on
Is there any function for me to set up color change for the row that I a
working on, so, I will not mistype row for my worksheet?
There are methods using sheetchange events but the easiest method is to
There are methods using sheetchange events but the easiest method is to us
Chip Pearson's RowLiner add-in
http://www.cpearson.com/excel/RowLiner.ht
Just a note...........Rowliner will not work on protected sheets
If your sheet is protected you need event code pasted into the sheet module
Right-click on the sheet tab and "View Code" Paste into that module
Private Sub Worksheet_SelectionChange(ByVal Target As Range
Static OldCell As Rang
If Application.CutCopyMode = 0 The
ActiveSheet.Unprotect Password:="justme
If Not OldCell Is Nothing The
With OldCell.EntireRo
..Interior.ColorIndex = xlColorIndexNon
..Borders.LineStyle = xlLineStyleNon
End Wit
End I
Set OldCell = Targe
With OldCell.EntireRo
..Interior.ColorIndex =
..EntireRow.Borders.LineStyle = xlContinuou
End Wit
Els
If OldCell Is Nothing The
Set OldCell = Targe
Els
Set OldCell = Union(OldCell, Target
End I
End I
ActiveSheet.Protect Password:="justme
End Su
Will color the active row yellow
Note: will wipe out existing background color of activecell cell unless B
color is due to C
Gord Dibben MS Excel MV
wrote:
And just for fun, if you do not want to do all 256 columns of the row
And just for fun, if you do not want to do all 256 columns of the row yo
selected, you could give this a try
Private Sub Worksheet_SelectionChange(ByVal Target As Range
Dim Data As Rang
Dim i As Intege
Dim j As Intege
Dim k As Intege
i =
j =
k = ActiveCell.Column(
Set Data = Range("B8:K22"
Data.Interior.ColorIndex = xlNon
If ActiveCell.Row < 8 Or ActiveCell.Row > 22 Or
ActiveCell.Column < 2 Or ActiveCell.Column > 11 The
Exit Su
End I
ActiveCell.Offset(0, -(k - i)).
Resize(1, 10).Interior.ColorIndex = 35 'try 26 it is is pretty wild!!
End Sub
Where this will highlight the row of the cell selected in the named range
Data which is Range(B8:K22). Of course the code would need to be tweeked to
suit your sheet operational range. If you have other parts of the sheet
with colored cells OUTSIDE the Data range then these will remain intact.
Chips code does not allow this but I believe there is code out there that
will retain the current formatting and still highlight your selected row and
return all the formatting when you reselect a cell. That is above my
paygrade and Chip and other MVP's can help you if that is needed.
Now with Chip Pearson's code if you select more that one cell in the column
then it will highlight all rows selected. That could be "gooder" if you
wanted several rows highlighted as your data may relate to stuff above and
below your primary row.
HTH
Regards
Howard
Whoa, I have confused Chips code with that of Gords!
Whoa, I have confused Chips code with that of Gords! And I believe most of
what I said about Chips code is false.
Would be fair to disregard my post but you may try the code I offered for a
limited range of row highlighting.
Sorry, really got ahead of myself here folks.
Regards,
Howard
Submitted via EggHeadCafe - Software Developer Portal of Choice
A Framework to Animate WPF and Silverlight Pages Similar to the PowerPoint Slides
http://www.eggheadcafe.com/tutorial...0-c7354940d878/a-framework-to-animate-wp.aspx
"will wipe out existing background color of active cell unless BG color is due to CF" Is it Conditional Formating?
I used the event code, but then al my background colours and lines changes to white. I do I stop that from happening?
I created a program for our school for the marks of each term and that is linked to a final report. The teachers are going to enter their marks. I protected my worksheet so that they wont be able to delete my formula's. Not everyone is on the same level with computer skills and highlighting the row will help them a lot when they enter the marks.
Thanks
Gord Dibben wrote:
There are methods using sheetchange events but the easiest method is to
05-Feb-10
There are methods using sheetchange events but the easiest method is to us
Chip Pearson's RowLiner add-in
http://www.cpearson.com/excel/RowLiner.ht
Just a note...........Rowliner will not work on protected sheets
If your sheet is protected you need event code pasted into the sheet module
Right-click on the sheet tab and "View Code" Paste into that module
Private Sub Worksheet_SelectionChange(ByVal Target As Range
Static OldCell As Rang
If Application.CutCopyMode = 0 The
ActiveSheet.Unprotect Password:="justme
If Not OldCell Is Nothing The
With OldCell.EntireRo
..Interior.ColorIndex = xlColorIndexNon
..Borders.LineStyle = xlLineStyleNon
End Wit
End I
Set OldCell = Targe
With OldCell.EntireRo
..Interior.ColorIndex =
..EntireRow.Borders.LineStyle = xlContinuou
End Wit
Els
If OldCell Is Nothing The
Set OldCell = Targe
Els
Set OldCell = Union(OldCell, Target
End I
End I
ActiveSheet.Protect Password:="justme
End Su
Will color the active row yellow
Note: will wipe out existing background color of activecell cell unless B
color is due to C
Gord Dibben MS Excel MV
wrote:
Previous Posts In This Thread:
automaticely highlight the row that I am working on
Is there any function for me to set up color change for the row that I a
working on, so, I will not mistype row for my worksheet?
There are methods using sheetchange events but the easiest method is to
There are methods using sheetchange events but the easiest method is to us
Chip Pearson's RowLiner add-in
http://www.cpearson.com/excel/RowLiner.ht
Just a note...........Rowliner will not work on protected sheets
If your sheet is protected you need event code pasted into the sheet module
Right-click on the sheet tab and "View Code" Paste into that module
Private Sub Worksheet_SelectionChange(ByVal Target As Range
Static OldCell As Rang
If Application.CutCopyMode = 0 The
ActiveSheet.Unprotect Password:="justme
If Not OldCell Is Nothing The
With OldCell.EntireRo
..Interior.ColorIndex = xlColorIndexNon
..Borders.LineStyle = xlLineStyleNon
End Wit
End I
Set OldCell = Targe
With OldCell.EntireRo
..Interior.ColorIndex =
..EntireRow.Borders.LineStyle = xlContinuou
End Wit
Els
If OldCell Is Nothing The
Set OldCell = Targe
Els
Set OldCell = Union(OldCell, Target
End I
End I
ActiveSheet.Protect Password:="justme
End Su
Will color the active row yellow
Note: will wipe out existing background color of activecell cell unless B
color is due to C
Gord Dibben MS Excel MV
wrote:
And just for fun, if you do not want to do all 256 columns of the row
And just for fun, if you do not want to do all 256 columns of the row yo
selected, you could give this a try
Private Sub Worksheet_SelectionChange(ByVal Target As Range
Dim Data As Rang
Dim i As Intege
Dim j As Intege
Dim k As Intege
i =
j =
k = ActiveCell.Column(
Set Data = Range("B8:K22"
Data.Interior.ColorIndex = xlNon
If ActiveCell.Row < 8 Or ActiveCell.Row > 22 Or
ActiveCell.Column < 2 Or ActiveCell.Column > 11 The
Exit Su
End I
ActiveCell.Offset(0, -(k - i)).
Resize(1, 10).Interior.ColorIndex = 35 'try 26 it is is pretty wild!!
End Sub
Where this will highlight the row of the cell selected in the named range
Data which is Range(B8:K22). Of course the code would need to be tweeked to
suit your sheet operational range. If you have other parts of the sheet
with colored cells OUTSIDE the Data range then these will remain intact.
Chips code does not allow this but I believe there is code out there that
will retain the current formatting and still highlight your selected row and
return all the formatting when you reselect a cell. That is above my
paygrade and Chip and other MVP's can help you if that is needed.
Now with Chip Pearson's code if you select more that one cell in the column
then it will highlight all rows selected. That could be "gooder" if you
wanted several rows highlighted as your data may relate to stuff above and
below your primary row.
HTH
Regards
Howard
Whoa, I have confused Chips code with that of Gords!
Whoa, I have confused Chips code with that of Gords! And I believe most of
what I said about Chips code is false.
Would be fair to disregard my post but you may try the code I offered for a
limited range of row highlighting.
Sorry, really got ahead of myself here folks.
Regards,
Howard
Submitted via EggHeadCafe - Software Developer Portal of Choice
A Framework to Animate WPF and Silverlight Pages Similar to the PowerPoint Slides
http://www.eggheadcafe.com/tutorial...0-c7354940d878/a-framework-to-animate-wp.aspx
L
Luke M
Correct, it refers to conditional formatting. What it's trying to say
(somewhat poorly) is that the script will change the background formatting
of the cell. However, CF formatting is a different property, and so cells
that are currently colored due to active CFs, will remain that color.
(somewhat poorly) is that the script will change the background formatting
of the cell. However, CF formatting is a different property, and so cells
that are currently colored due to active CFs, will remain that color.