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