ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Formatting with Macros (https://www.excelbanter.com/excel-programming/403922-formatting-macros.html)

gator
Formatting with Macros
 
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


--
---
HTH

Bob

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

--
Gator

Bob Phillips
Formatting with Macros
 
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)



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


--
---
HTH

Bob

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

--
Gator



JLGWhiz
Formatting with Macros
 
You should be able to clear it with FormatConditionalFormattingDelete on
the standard menu bar.

"Gator" wrote:

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


--
---
HTH

Bob

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

--
Gator

gator
Formatting with Macros
 
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
--
Gator


"Bob Phillips" wrote:

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)



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


--
---
HTH

Bob

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

--
Gator




Bob Phillips
Formatting with Macros
 
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)



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


"Bob Phillips" wrote:

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)



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


--
---
HTH

Bob

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

--
Gator

gator
Formatting with Macros
 
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?
--
Gator


"Bob Phillips" wrote:

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)



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


"Bob Phillips" wrote:

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)



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


--
---
HTH

Bob

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

--
Gator

feltra
Formatting with Macros
 
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


On Jan 9, 9:56 pm, Gator wrote:
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.


All times are GMT +1. The time now is 06:08 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com