Code to format cells on several worksheets

  • Thread starter Thread starter alanstevenson70
  • Start date Start date
A

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
 
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.
Click to expand...

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.
 
Back
Top