0

I am currently trying to apply this code below to active sheets by converting it to macro. However I am having trouble with for each function.

This is the original code. 

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    'Update 20140318
    Static xRow
    Static xColumn
    If xColumn <> "" Then
        With Columns(xColumn).Interior
            .ColorIndex = xlNone
        End With
        With Rows(xRow).Interior
            .ColorIndex = xlNone
        End With
    End If
    pRow = Selection.Row
    pColumn = Selection.Column
    xRow = pRow
    xColumn = pColumn
    With Columns(pColumn).Interior
        .ColorIndex = 22
        .Pattern = xlSolid
    End With
    With Rows(pRow).Interior
        .ColorIndex = 6
        .Pattern = xlSolid
    End With
End Sub

My unsuccessful attempt to convert : 

Sub Highlighter()
'
' Highlighter Macro
'
' Keyboard Shortcut: Ctrl+Shift+H
'
Dim xSheet As Worksheet

For Each xSheet In This.Workbook.Worksheets
    xSheet.Select
    Static xRow
    Static xColumn

    If xColumn <> "" Then
        With Columns(xColumn).Interior
            .ColorIndex = xlNone
        End With
        With Rows(xRow).Interior
            .ColorIndex = xlNone
        End With
    End If
    pRow = Selection.Row
    pColumn = Selection.Column
    xRow = pRow
    xColumn = pColumn
    With Columns(pColumn).Interior
        .ColorIndex = 22
        .Pattern = xlSolid
    End With
    With Rows(pRow).Interior
        .ColorIndex = 6
        .Pattern = xlSolid



Next xSheet

End Sub

Please help! Thanks!

Third attempt >> It is working but how to do Selection Change within workbook to apply to all sheets? 

Sub Highlighter()
'
' Highlighter Macro
'
' Keyboard Shortcut: Ctrl+Shift+H
'


Dim xSheet As Worksheet

For Each xSheet In ActiveWorkbook.Worksheets


xSheet.Select
Static xRow
Static xColumn

If xColumn <> "" Then
    With xSheet.Columns(xColumn).Interior
        .ColorIndex = xlNone
    End With
    With xSheet.Rows(xRow).Interior
        .ColorIndex = xlNone
    End With
End If
pRow = Selection.Row
pColumn = Selection.Column
xRow = pRow
xColumn = pColumn
With xSheet.Columns(pColumn).Interior
    .ColorIndex = 22
    .Pattern = xlSolid
End With
With xSheet.Rows(pRow).Interior
    .ColorIndex = 6
    .Pattern = xlSolid

End With
Gerle Batde
  • 183
  • 3
  • 21
  • _To all active sheets_ makes no sense. Excel can only have one active sheet. Did you mean all sheets in the workbook? – litelite Sep 13 '16 at 16:44
  • What problem do you have. Does it run but have the wrong output, does it run and does nothing, does it crash? – litelite Sep 13 '16 at 16:59
  • Yes. I meant all sheets in the workbook. As long as the workbook is running in bg, I want this vba code to run – Gerle Batde Sep 13 '16 at 16:59
  • You should probably rename your question accordingly then. (Maybe something like ... to all sheets in workbook) – litelite Sep 13 '16 at 17:07
  • 1
    Another couple things to note: 1) You never declare `pRow` and `pColumn` 2) You declare `xRow` and `xColumn` as `Static` and before you set a value to them, you check if they are blank 3) See comment 2, except you declare them as `Static` and then try to change them – Kyle Sep 13 '16 at 17:08
  • @Kyle i think that `xRow` and `xColumn` act as a memory for the previous selection. and that would be why they are checked before being assigned once. – litelite Sep 13 '16 at 17:12
  • @litelite, fair enough, but they should be assigned before entering the loop then, no? Isn't the purpose to highlight the same cell (ie selected cell) in each sheet? They are being reassigned every time, and are using the dreaded `Selection` to do so. – Kyle Sep 13 '16 at 17:28
  • @Kyle she wants to higlight the selection. so using `Selection` is correct there. And there is nothing in the question that says it should always be the same cell. I think that it is normal for the highligted cell to change from sheet to sheet. But the information that should be caried by the static variables is always lost due to the loop. And i think that that is a problem. – litelite Sep 13 '16 at 17:33
  • Well, the above will highlight something on every sheet, and it is not specified that it should not be the same cell. I suppose I assumed the "apply it to every sheet" comment by the OP meant to loop sheets and highlight cells on all of them. If it needs to be on only the activesheet, this is largely the wrong approach. – Kyle Sep 13 '16 at 17:40

4 Answers4

0

Replace 

This.Workbook.Worksheets

By 

ThisWorkbook.Worksheets

The object This does not exists in vba. However there is a global property called ThisWorkbook (in one word).

However, your code have an other problem, which is the use of static variables. The way you are doing it right now will only save the selection for the last sheet. You will need to find another way to store that information. one way that would require minimal code change is to use dictionaries that will allow you to store multiple values and associate them with a specific sheet.

Community
  • 1
  • 1
litelite
  • 2,857
  • 4
  • 23
  • 33
0

Paste this into a module:

Sub Highlight()

Dim xSheet As Worksheet

For Each xSheet In Worksheets

    xSheet.Select

    With xSheet.Cells.Interior
        .ColorIndex = xlNone
    End With

    With xSheet.Columns(Selection.Column).Interior
        .ColorIndex = 22
        .Pattern = xlSolid
    End With
    With xSheet.Rows(Selection.Row).Interior
        .ColorIndex = 6
        .Pattern = xlSolid
    End With


Next xSheet

End Sub

Paste this into the code of every worksheet:

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    Call Highlight
End Sub

If you want to add code to the Worksheet_SelectionChange event of a worksheet through code, see the following question: Excel vba add code to sheet module programmatically

Community
  • 1
  • 1
slayernoah
  • 4,382
  • 11
  • 42
  • 73
0

Your code works great for any single sheet if it is place in the worksheet code area for that sheet.

If you want the code to work on several sheets, it must be placed in the worksheet code area of each sheet.

A loop will not do.

Gary's Student
  • 95,722
  • 10
  • 59
  • 99
  • It will eventally work. She just need to call the method in the event of each worksheet individually. And i think it is correct to have a single sub and call it from several place instead of copying the code. (i would avoid to do all sheets all the time in a loop and prefer to pass the correct selection/previous selection to the sub instead) – litelite Sep 13 '16 at 17:25
  • You are correct! I can loop this and activate it in every active sheet but without selection change it won't work. Do you know how? – Gerle Batde Sep 13 '16 at 17:25
0

Put the following in a module:

Sub Highlight(ws As Worksheet, xRow As Long, xColumn As Long)
    'Clear previous formatting
    If xColumn > 0 Then
        With ws.Columns(xColumn).Interior
            .ColorIndex = xlNone
        End With
        With ws.Rows(xRow).Interior
            .ColorIndex = xlNone
        End With
    End If

    pRow = Selection.Row
    pColumn = Selection.Column
    xRow = pRow
    xColumn = pColumn
    With ws.Columns(pColumn).Interior
        .ColorIndex = 22
        .Pattern = xlSolid
    End With
    With ws.Rows(pRow).Interior
        .ColorIndex = 6
        .Pattern = xlSolid
    End With
End Sub

And put the following into every worksheet:

Private xRow As Long
Private xColumn As Long

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    Call Highlight(Me, xRow, xColumn)
End Sub

Each worksheet will then keep track of the "previous selection" specific to that sheet, and those values will be passed to the common Highlight subroutine.

One warning: When the workbook is saved, the highlights will be saved as well. When the workbook is re-opened, the "previous selection" will no longer be known by the macros. You could possibly add something to the Workbook_Open event to set each sheet's initial xRow and xColumn values.

YowE3K
  • 23,852
  • 7
  • 26
  • 40