Code execution

[excelsean]

Yesterday was my first post. Thank you 'Brian in Maui' for your reply.

Today I'm inserting canned code into a worksheet module macro. When I drop it in an individual sheet, it works great. When I drop it in "This Workbook" the code does not function. If I want the code to function for every sheet, how can one do it?

Thanks!

Sean.

 

[stevebausch]

Maybe you can't park the code in one place, for all sheets.

What's the name of the routine(s)?

You may just need to take the code, put it in a routine of your naming, and call that routine from the event.

 

[excelsean]

Hello Steve;

What I'm doing is inserting the color band code from the hall of fame section of this site. When I drop the code into "sheet1", it works great only sheet1. When I drop the code into "thisworkbook", it doesn't work in any sheet. I want it to work in all sheets with the least amount of effort. Any suggestions. (p.s. I'm a newbie)

Talk in short sentences.<grin>

Thanks!

Sean.

 

[stevebausch]

Okay, found your post. You are working with Worksheet_SelectionChange, which is specific to each sheet.

You would need to paste the core code into a routine of your own naming, in ThisWorkbook. Let's call it DoTheBars

Then, in each _SelectionChange event, insert this line:
ThisWorkbook.DoTheBars

That is, IF the code is currently robust enough to figure out what sheet you are trying to reference.

 

[stevebausch]

In each worksheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ThisWorkbook.DoTheBars Target
End Sub


In ThisWorkbook:

Sub DoTheBars(Target As Range)
Dim iColor As Integer
'// Amended routine found on this Web site
'// Note: Don't use IF you have Conditional
'// formating that you want to keep!

'// On error resume in case
'// user selects a range of cells
On Error Resume Next
iColor = Target.Interior.ColorIndex
'Leave On Error ON for Row offset errors

If iColor < 0 Then
iColor = 36
Else
iColor = iColor + 1
End If

'// Need this test incase Font color is the same
If iColor = Target.Font.ColorIndex Then iColor = iColor + 1

Cells.FormatConditions.Delete

'// Horizontal color banding
With Range("A" & Target.Row, Target.Address) 'Rows(Target.Row)
.FormatConditions.Add Type:=2, Formula1:="TRUE"
.FormatConditions(1).Interior.ColorIndex = iColor
End With

End Sub

 

[excelsean]

Steve;

I copied the large string of code to sheet 1, and the short string of code to page 2.

Got an error message for page 2. First line of code was yellowed. "DoTheBars" was also yellowed in line 2.

any clues?

 

[stevebausch]

Follow the instructions of my (next to last) post.

 

[Legacy 98055]

Why can't you use a Workbook level event?
This will apply to every worksheet. Place in the workbook module.

<pre>
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Dim iColor As Integer
'// Amended routine found on this Web site
'// Note: Don't use IF you have Conditional
'// formating that you want to keep!

'// On error resume in case
'// user selects a range of cells
On Error Resume Next
With Sh
iColor = Target.Interior.ColorIndex
'Leave On Error ON for Row offset errors

If iColor < 0 Then
iColor = 36
Else
iColor = iColor + 1
End If

'// Need this test incase Font color is the same
If iColor = Target.Font.ColorIndex Then iColor = iColor + 1

.Cells.FormatConditions.Delete

'// Horizontal color banding
With .Range("A" & Target.Row, Target.Address) 'Rows(Target.Row)
.FormatConditions.Add Type:=2, Formula1:="TRUE"
.FormatConditions(1).Interior.ColorIndex = iColor
End With

'// Vertical color banding
With .Range(Target.Offset(1 - Target.Row, 0).Address & ":" & Target.Offset(-1, 0).Address) 'Rows(.Target.Row)
.FormatConditions.Add Type:=2, Formula1:="TRUE"
.FormatConditions(1).Interior.ColorIndex = iColor
End With
End With
End Sub

</pre>

Tom

 

[stevebausch]

Tom is correct; you can have every sheet covered with his technique.

If there would be an 'exception to the rule' worksheet, you could use my technique.

Steve

 

[excelsean]

TsTom;

You're the Man!
Thanks!

Steve; Thank you too!
Good day from San Diego!

Sean.