Bob
what code can I use that will disable the macro and clear the formatting?
--
Gator


"Bob Phillips" wrote:

Assuming that the target cell is the activecell when the icon is clicked,
then in a standard module create

'----------------------------------------------------------------
Public Sub SetHighlight(ByVal Target As Range)
'----------------------------------------------------------------
Cells.FormatConditions.Delete
With Target
.EntireRow.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
AddRowBorders Target.EntireRow
With .EntireColumn
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
End With
AddColumnBorders Target.EntireColumn

.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
.FormatConditions(1).Interior.ColorIndex = 36
End With

End Sub

Private Sub AddRowBorders(pRow As Range)
With pRow
With .FormatConditions(1)
With .Borders(xlTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
With .Borders(xlBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
.Interior.ColorIndex = 20
End With
End With
End Sub

Private Sub AddColumnBorders(pColumn As Range)
With pColumn
With .FormatConditions(1)
With .Borders(xlLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
With .Borders(xlRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
.Interior.ColorIndex = 20
End With
End With
End Sub


then add a new macro in a standrad code module

Public Sub Icon_Click()
Call SetHighlight(Activecell)
End Sub

and finally change the worksheet change event to


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Call SetHighlight(Target)
End Sub


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Gator" wrote in message
...
Here is the code I have. How do I tie three sub procedures into one
macro?

'----------------------------------------------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'----------------------------------------------------------------
Cells.FormatConditions.Delete
With Target
.EntireRow.FormatConditions.Add Type:=xlExpression,
Formula1:="TRUE"
AddRowBorders Target.EntireRow
With .EntireColumn
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
End With
AddColumnBorders Target.EntireColumn

.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
.FormatConditions(1).Interior.ColorIndex = 36
End With

End Sub

Private Sub AddRowBorders(pRow As Range)
With pRow
With .FormatConditions(1)
With .Borders(xlTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
With .Borders(xlBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
.Interior.ColorIndex = 20
End With
End With
End Sub

Private Sub AddColumnBorders(pColumn As Range)
With pColumn
With .FormatConditions(1)
With .Borders(xlLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
With .Borders(xlRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 5
End With
.Interior.ColorIndex = 20
End With
End With
End Sub


--
Gator


"Gary''s Student" wrote:

We can do this in two steps:

1. transfer the logic from the event macro to a public macro
2. assign the public macro to an icon or shape

Here is an example:

we start with:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox ("Hello World")
End Sub

The first thing to to create a public macro in a standard module:

Public Sub gator()
MsgBox ("hello World")
End Sub

and the old event macro gets modified to:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
call gator
End Sub

At this point any routine can call gator. Insert any picture in the
worksheet; right-click the picture and assign it the sub gator

--
Gary''s Student - gsnu200763


"Gator" wrote:

I have a Private Sub named Worksheet_SelectionChange(ByVal Target As
Range)
How do I create a macro to run this code. I am trying to assign VB
Code to
run when I click an icon that I made. I get stuck when I have to
assign a
macro to the icon. Do I just use the sub name above? Do I create a
macro to
run the sub and then assign the icon to the macro?
HELP
--
Gator