Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Restrict Highlighting?
I've seen this from Bob Phillips:
'-----------------------------*------------------------------*----- Private Sub WorkSheet_SelectionChange(ByVa*l Target As Range) '-----------------------------*------------------------------*----- Cells.FormatConditions.Delete With Target.EntireRow .FormatConditions.Add Type:=xlExpression, Formula1:="TRUE" .FormatConditions(1).Interior.*ColorIndex = 35 End With With Target.EntireColumn .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, Formula1:="TRUE" .FormatConditions(1).Interior.*ColorIndex = 35 End With With Target .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, Formula1:="TRUE" .FormatConditions(1).Interior.*ColorIndex = 36 End With End Sub How can I restrict it to specific range - Range("J5:S55") -- David |
#2
|
|||
|
|||
'-----------------------------*------------------------------*-----
Private Sub WorkSheet_SelectionChange(ByVal Target As Range) '-----------------------------*------------------------------*----- If Not Intersect(Target, Me.Range("J5:S55")) Is Nothing Then Cells.FormatConditions.Delete With Target.EntireRow .FormatConditions.Add Type:=xlExpression, Formula1:="TRUE" .FormatConditions(1).Interior.ColorIndex = 35 End With With Target.EntireColumn .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, Formula1:="TRUE" .FormatConditions(1).Interior.ColorIndex = 35 End With With Target .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, Formula1:="TRUE" .FormatConditions(1).Interior.ColorIndex = 36 End With End If End Sub -- HTH RP (remove nothere from the email address if mailing direct) "David" wrote in message ... I've seen this from Bob Phillips: '-----------------------------*------------------------------*----- Private Sub WorkSheet_SelectionChange(ByVa*l Target As Range) '-----------------------------*------------------------------*----- Cells.FormatConditions.Delete With Target.EntireRow .FormatConditions.Add Type:=xlExpression, Formula1:="TRUE" .FormatConditions(1).Interior.*ColorIndex = 35 End With With Target.EntireColumn .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, Formula1:="TRUE" .FormatConditions(1).Interior.*ColorIndex = 35 End With With Target .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, Formula1:="TRUE" .FormatConditions(1).Interior.*ColorIndex = 36 End With End Sub How can I restrict it to specific range - Range("J5:S55") -- David |
#3
|
|||
|
|||
Bob Phillips wrote
'-----------------------------ð------------------------------ð----- Private Sub WorkSheet_SelectionChange(ByVal Target As Range) '-----------------------------ð------------------------------ð----- If Not Intersect(Target, Me.Range("J5:S55")) Is Nothing Then Cells.FormatConditions.Delete With Target.EntireRow .FormatConditions.Add Type:=xlExpression, Formula1:="TRUE" .FormatConditions(1).Interior.ColorIndex = 35 End With With Target.EntireColumn .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, Formula1:="TRUE" .FormatConditions(1).Interior.ColorIndex = 35 End With With Target .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, Formula1:="TRUE" .FormatConditions(1).Interior.ColorIndex = 36 End With End If End Sub Thanks for the quick reply. Still highlights entire row and column of Target, though. Guess I should have said restrict *within* Range ("J5:S55") Anyway, I was able to modify with my existing code (I have some other conditions that need to be satisfied) and came up with: Range("I5:S55").Interior.ColorIndex = xlNone If Intersect(Target, Range("J5:S55")) Is Nothing Then Exit Sub Range(Cells(Target.Row, 9), Cells(Target.Row, 19)).Interior.ColorIndex = 35 Range(Cells(5, Target.Column), Cells(55, _ Target.Column)).Interior.ColorIndex = 35 ActiveCell.Interior.ColorIndex = 36 Seems to do the trick. -- David |
#4
|
|||
|
|||
I don't like coding those cells more than you have to.
You didn't say you didn't want the whole row and column, just restrict it. If J5 was selected, what should be highlighted? etc. -- HTH RP (remove nothere from the email address if mailing direct) "David" wrote in message ... Bob Phillips wrote '-----------------------------ð------------------------------ð----- Private Sub WorkSheet_SelectionChange(ByVal Target As Range) '-----------------------------ð------------------------------ð----- If Not Intersect(Target, Me.Range("J5:S55")) Is Nothing Then Cells.FormatConditions.Delete With Target.EntireRow .FormatConditions.Add Type:=xlExpression, Formula1:="TRUE" .FormatConditions(1).Interior.ColorIndex = 35 End With With Target.EntireColumn .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, Formula1:="TRUE" .FormatConditions(1).Interior.ColorIndex = 35 End With With Target .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, Formula1:="TRUE" .FormatConditions(1).Interior.ColorIndex = 36 End With End If End Sub Thanks for the quick reply. Still highlights entire row and column of Target, though. Guess I should have said restrict *within* Range ("J5:S55") Anyway, I was able to modify with my existing code (I have some other conditions that need to be satisfied) and came up with: Range("I5:S55").Interior.ColorIndex = xlNone If Intersect(Target, Range("J5:S55")) Is Nothing Then Exit Sub Range(Cells(Target.Row, 9), Cells(Target.Row, 19)).Interior.ColorIndex = 35 Range(Cells(5, Target.Column), Cells(55, _ Target.Column)).Interior.ColorIndex = 35 ActiveCell.Interior.ColorIndex = 36 Seems to do the trick. -- David |
#5
|
|||
|
|||
Bob Phillips wrote
I don't like coding those cells more than you have to. You didn't say you didn't want the whole row and column, just restrict it. If J5 was selected, what should be highlighted? etc. Sorry, I'm still learning how to post my needs clearly. I never seem to get it right the first time 8-( -- David |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cells in Excel will not stop highlighting | Excel Discussion (Misc queries) | |||
How do I restrict only 1 of 2 check boxes be checked? | Excel Discussion (Misc queries) | |||
Help with Highlighting all duplicates in a row | Excel Discussion (Misc queries) | |||
Restrict input if amount is exceeded | Excel Worksheet Functions | |||
restrict opening Excel file residing on a shared drive to one user | Excel Discussion (Misc queries) |