A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

automaticely highlight the row that I am working on



 
 
Thread Tools Display Modes
  #1  
Old February 6th, 2010, 01:29 AM posted to microsoft.public.excel.worksheet.functions
Cindy
external usenet poster
 
Posts: 385
Default 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 am
working on, so, I won't mistype row for my worksheet?
  #2  
Old February 6th, 2010, 02:00 AM posted to microsoft.public.excel.worksheet.functions
Gord Dibben
external usenet poster
 
Posts: 20,252
Default automaticely highlight the row that I am working on

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

On Fri, 5 Feb 2010 16:29:01 -0800, Cindy
wrote:

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?


  #3  
Old February 6th, 2010, 04:16 AM posted to microsoft.public.excel.worksheet.functions
L. Howard Kittle
external usenet poster
 
Posts: 516
Default automaticely highlight the row that I am working on

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

"Cindy" wrote in message
...
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?



  #4  
Old February 6th, 2010, 05:20 AM posted to microsoft.public.excel.worksheet.functions
L. Howard Kittle
external usenet poster
 
Posts: 516
Default automaticely highlight the row that I am working on

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

"Cindy" wrote in message
...
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?



  #5  
Old April 19th, 2010, 03:45 PM posted to microsoft.public.excel.worksheet.functions
Eurika Stemmet
external usenet poster
 
Posts: 3
Default Highlighting a row

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 use
Chip Pearson's RowLiner add-in.

http://www.cpearson.com/excel/RowLiner.htm

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

wrote:

Previous Posts In This Thread:

On Friday, February 05, 2010 7:29 PM
Cindy wrote:

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 am
working on, so, I will not mistype row for my worksheet?

On Friday, February 05, 2010 8:00 PM
Gord Dibben wrote:

There are methods using sheetchange events but the easiest method is to
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 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 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

wrote:

On Friday, February 05, 2010 10:16 PM
L. Howard Kittle 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 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 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

On Friday, February 05, 2010 11:20 PM
L. Howard Kittle wrote:

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/tutorials...nimate-wp.aspx
  #6  
Old April 19th, 2010, 05:02 PM posted to microsoft.public.excel.worksheet.functions
Luke M[_4_]
external usenet poster
 
Posts: 451
Default Highlighting a row

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.

--
Best Regards,

Luke M
Eurika Stemmet wrote in message
...
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
use
Chip Pearson's RowLiner add-in.

http://www.cpearson.com/excel/RowLiner.htm

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

wrote:

Previous Posts In This Thread:

On Friday, February 05, 2010 7:29 PM
Cindy wrote:

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 am
working on, so, I will not mistype row for my worksheet?

On Friday, February 05, 2010 8:00 PM
Gord Dibben wrote:

There are methods using sheetchange events but the easiest method is to
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 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 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

wrote:

On Friday, February 05, 2010 10:16 PM
L. Howard Kittle 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 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 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

On Friday, February 05, 2010 11:20 PM
L. Howard Kittle wrote:

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/tutorials...nimate-wp.aspx



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:16 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.