Code to format cells on several worksheets


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
 
Ad

Advertisements

C

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.
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top