Code to format cells on several worksheets
 

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

Code to format cells on several worksheets
 

Hi Steve,

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

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.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Code to format cells on several worksheets
 

Claus

That worked a treat! Thank you very much

Steve

On Wednesday, January 22, 2014 2:56:44 PM UTC, Claus Busch wrote:

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.

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2