Code to format cells on several worksheets

[alanstevenson70]

Hi

I have written the code below to ensure some key info is always visible in cells whether or not users unprotect the worksheet and delete the contents. The code is password protected.

Private Sub Workbook_Open()
Sheets("Sheet1").Select
Range("A1").Font.ColorIndex = 51
Range("A1:B1").Interior.Color = RGB(211, 206, 177)
Range("A1").Value = "KEEP THIS TEXT"
Selection.Locked = True
End Sub

The problem I have is, I can make this work on 1 sheet, but don't have a clue how to use it on other worksheets (eg, Sheet2, Sheet3, Sheet4, etc) in the workbook without repeating the code and changing the sheet reference.

The relevant cell is the same on every sheet, A1.

Please can someone advise how to modify the vb so I can do this?

Thanks - help would be much appreciated

Steve

 

[Claus Busch]

Hi Steve,

Am Wed, 22 Jan 2014 06:45:40 -0800 (PST) schrieb
(e-mail address removed):

The problem I have is, I can make this work on 1 sheet, but don't have a clue how to use it on other worksheets (eg, Sheet2, Sheet3, Sheet4, etc) in the workbook without repeating the code and changing the sheet reference.

The relevant cell is the same on every sheet, A1.

try this code in the code module of "ThisWorkbook":

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
With Sh
With .Range("A1")
.Font.ColorIndex = 51
.Value = "KEEP THIS TEXT"
End With
With .Range("A1:B1")
.Interior.Color = RGB(211, 206, 177)
.Locked = True
End With
End With
End Sub


Regards
Claus B.

 

[alanstevenson70]

Claus

That worked a treat! Thank you very much

Steve