Formatting with Macros

  • Thread starter Thread starter Gator
  • Start date Start date
G

Gator

what code can I use to assign to a macro that will disable the following
macro and clear the formatting? When I enable and use the macro and then
close the workbook and then open the workbook, even if I disable the macro,
the background and border formatting is still there and I can't clear it
using the standard formatting toolbar. I want to be able to turn on and off
the macro and clear the formatting. The following code enables the macro,
now I need one to disbale it.
--
Gator

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


--
Click to expand...

Was this post helpful to you?

Why should I rate a post?



Expand AllCollapse All

Manage Your Profile |Contact us
© 2008 Microsoft Corporation. All rights reserved. Terms of Use |Trademarks
|Privacy Statement
 
Disable the macro? remove it.

Clear the formatting

Public Sub ClearHighlight()
Activesheet.Cells.FormatConditions.Delete
End Sub

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
You should be able to clear it with Format>ConditionalFormatting>Delete on
the standard menu bar.
 
I want to be able to turn the macro on and off. When I enable the macro, the
formatting works and continues to work - which is what I want it to do. I
want to be able use another icon that disables the macro and clears the
formatting, without having to remove it or closing the sheet, because I might
want to turn it back on. It's like, for the same sheet, sometimes I want it
and sometimes I don't - which could be several times in a day or in an hour.
Thanks
 
I once created such an app. I will try and dig it out for you.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
That would be great. Also, I tried your code to clear the formatting and
can't get it to work. I put the code in a standard module and assigned it to
an icon by right clicking the icon and customize and assign macro and clicked
on the module name.
Do I need to to something else?
 
Hi,

There are probably more elegant solutions, but for a quick fix, cant
you use some module level globals to selectively set/unset and then
execute the code within the macros based on the true/false of those
booleans?

Best Regards,
-feltra
 
Back
Top