An idea to assist

[mole999]

OK, I create a complete new set of sheets and pivots when I update, apply formatting as its built which is fine. Problem is I'm using highlighting on the Pivot Filter to ensure all users can see where to look which is also fine, UNTIL the filter is changed.

As the new sheets have no code on them, is there a sensible way to monitor for worksheet change from ThisWorkbook or a module, so that once the sheet has changed the formatting will be reapplied

 

[MUKESHY12390]

I'm trying to understand... you want to apply that "Yellow and Red border" next to pivotTable.

sheet has changed -- are you talking about Navigation or value/formatting.

 

[mole999]

when I use the drop down to change the display of data the Red/Yellow are overwritten

As the sheet is created new every time I run the update the sheet has NO data on its module. When first built this is applied to the sheet, around the area shown

Public Sub thickblockredborder()
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Color = RGB(255, 0, 0)
        .Weight = xlThick
    End With
        With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Color = RGB(255, 0, 0)
        .Weight = xlThick
    End With
        With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Color = RGB(255, 0, 0)
        .Weight = xlThick
    End With
        With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Color = RGB(255, 0, 0)
        .Weight = xlThick
   End With

    With Selection.Interior
        .Pattern = xlSolid
         .Color = RGB(255, 255, 0)
    End With
   End Sub

But I need to transfer

Private Sub Worksheet_Change(ByVal Target As Range)
thickblockredborder
End Sub

to the designated sheet so it can monitor and update, as changes are applied by end users

It should be possible with applications extensibility 5.3 just can't figure out how to target a newly created sheet to have it added, to get it triggered

 

[MUKESHY12390]

Perhaps this ....

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)



 '''' "Mapping_sheet is just  sheet where you don't want to perform any action
  If Sh.Name <> "Mapping_sheet" Then
  
   
     ''' call here
      MsgBox "thickblockredborder"
   
    
  
  Else
   '''
    'No Action
  
  End If


End Sub

 

[mole999]

Perhaps this ....

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

'''' "Mapping_sheet is just  sheet where you don't want to perform any action
  If Sh.Name <> "Mapping_sheet" Then
  
     ''' call here
      MsgBox "thickblockredborder"
 
   
 
  Else
   '''
    'No Action
 
  End If


End Sub

Does that not have to be already installed on the sheet where the action is taking place, Doh jus reread and see its workbook, in which case that should work really well. Thank you

 

[mole999]

@[U]MUKESHY12390[/U] did exactly what I wanted to do, with less fuss

 

[MUKESHY12390]

I'm glad it worked out.