imback2nite
Board Regular
- Joined
- Oct 30, 2004
- Messages
- 203
- Office Version
- 2003 or older
- Platform
- Windows
Thank you for reading. I'm using this code and I was hoping to be able to use it on selected pages. For example, if I only wanted to use it on worksheets 1, 3 and 5. Pages 5 thru 10. Or even if the pages I want it to work had had something unique that all the other pages didn't like cell B1 had "Name" in it. Preferably I would like to be able to name the sheets like, Sheet1, Sheet3 etc. but I'll take it anyway that it will work! The reason I'm doing this is to hopefully speed the workbook along and to shrink it. It's already at 5 megabytes. Once again thank you for reading this.
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
Dim Rng1 As Range
If Target.Address = "$D$1" Then
ActiveSheet.Name = Left(Target.Value, 35)
Exit Sub
End If
On Error Resume Next
Set Rng1 = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 1)
On Error GoTo 0
If Rng1 Is Nothing Then
Set Rng1 = Range(Target.Address)
Else
Set Rng1 = Union(Range(Target.Address), Rng1)
End If
For Each cell In Rng1
Select Case cell.Value
Case vbNullString
cell.Interior.ColorIndex = xlNone
cell.Font.Bold = False
Case Range("AW9").Value
cell.Interior.ColorIndex = 6
cell.Font.Bold = True
cell.Font.ColorIndex = 1
Case Else
cell.Interior.ColorIndex = xlNone
cell.Font.Bold = False
End Select
Next
End Sub