Worksheet Change on multiple sheets

imback2nite

imback2nite

Board Regular
Joined
Oct 30, 2004
Messages
211
Office Version
  1. 2003 or older
Platform
  1. Windows
I have this code on Sheets 13 through 42. IS there a way I can write this code once and have it react on just these sheets? Thank you in advance!!

VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

Dim cell As Range
Dim Rng1 As Range
 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("H3").Value
            cell.Interior.ColorIndex = 6
            cell.Font.Bold = True
            cell.Font.ColorIndex = 1
        Case Range("H4").Value
            cell.Interior.ColorIndex = 43
            cell.Font.Bold = True
            cell.Font.ColorIndex = 1
        Case Range("H5").Value
            cell.Interior.ColorIndex = 54
            cell.Font.Bold = True
            cell.Font.ColorIndex = 2
        Case Range("H6").Value
            cell.Interior.ColorIndex = 39
            cell.Font.Bold = True
            cell.Font.ColorIndex = 1
        Case Range("H7").Value
            cell.Interior.ColorIndex = 2
            cell.Font.Bold = True
            cell.Font.ColorIndex = 1
        Case Range("H8").Value
            cell.Interior.ColorIndex = 30
            cell.Font.Bold = True
            cell.Font.ColorIndex = 2
        Case Range("H9").Value
            cell.Interior.ColorIndex = 39
            cell.Font.Bold = True
            cell.Font.ColorIndex = 1
        Case Range("P3").Value
            cell.Interior.ColorIndex = 26
            cell.Font.Bold = True
            cell.Font.ColorIndex = 1
        Case Range("P4").Value
            cell.Interior.ColorIndex = 46
            cell.Font.Bold = True
            cell.Font.ColorIndex = 1
        Case Range("P5").Value
            cell.Interior.ColorIndex = 3
            cell.Font.Bold = True
            cell.Font.ColorIndex = 2
        Case Range("P6").Value
            cell.Interior.ColorIndex = 53
            cell.Font.Bold = True
            cell.Font.ColorIndex = 2
        Case Range("P7").Value
            cell.Interior.ColorIndex = 29
            cell.Font.Bold = True
            cell.Font.ColorIndex = 2
        Case Range("P8").Value
            cell.Interior.ColorIndex = 40
            cell.Font.Bold = True
            cell.Font.ColorIndex = 1
        Case Range("P9").Value
            cell.Interior.ColorIndex = 10
            cell.Font.Bold = True
            cell.Font.ColorIndex = 2
        Case Range("Y3").Value
            cell.Interior.ColorIndex = 35
            cell.Font.Bold = True
            cell.Font.ColorIndex = 1
        Case Range("Y4").Value
            cell.Interior.ColorIndex = 10
            cell.Font.Bold = True
            cell.Font.ColorIndex = 2
        Case Range("Y5").Value
            cell.Interior.ColorIndex = 5
            cell.Font.Bold = True
            cell.Font.ColorIndex = 2
        Case Range("Y6").Value
            cell.Interior.ColorIndex = 16
            cell.Font.Bold = True
            cell.Font.ColorIndex = 1
        Case Range("Y7").Value
            cell.Interior.ColorIndex = 42
            cell.Font.Bold = True
            cell.Font.ColorIndex = 1
        Case Range("Y8").Value
            cell.Interior.ColorIndex = 4
            cell.Font.Bold = True
            cell.Font.ColorIndex = 1
        Case Range("Y9").Value
            cell.Interior.ColorIndex = 46
            cell.Font.Bold = True
            cell.Font.ColorIndex = 2
        Case Range("AF3").Value
            cell.Interior.ColorIndex = 34
            cell.Font.Bold = True
            cell.Font.ColorIndex = 1
        Case Range("AF4").Value
            cell.Interior.ColorIndex = 38
            cell.Font.Bold = True
            cell.Font.ColorIndex = 1
        Case Range("AF5").Value
            cell.Interior.ColorIndex = 8
            cell.Font.Bold = True
            cell.Font.ColorIndex = 1
        Case Range("AF6").Value
            cell.Interior.ColorIndex = 47
            cell.Font.Bold = True
            cell.Font.ColorIndex = 2
        Case Range("AF7").Value
            cell.Interior.ColorIndex = 12
            cell.Font.Bold = True
            cell.Font.ColorIndex = 2
        Case Range("AF8").Value
            cell.Interior.ColorIndex = 33
            cell.Font.Bold = True
            cell.Font.ColorIndex = 1
        Case Range("AF9").Value
            cell.Interior.ColorIndex = 53
            cell.Font.Bold = True
            cell.Font.ColorIndex = 2
        Case Else
            cell.Interior.ColorIndex = xlNone
            cell.Font.Bold = False
        End Select
    Next
End Sub
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Sort by date Sort by votes
There is a lot of code here:
But here is a simple example use select case

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on any sheet tab. Not in every sheet tab. Just do it once
Select View Code from the pop-up context menu
Double click on "ThisWorkbook"
Paste the code in the VBA edit window
In my code it will set the cell color to green when you make any change to any value in column 1 of sheets 13 to42

But paste in your code:
VBA Code: 
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'Modified  8/27/2022  10:18:11 PM  EDT

    Select Case ActiveSheet.Index
        Case 13 To 42
            If Target.Column = 1 Then
                Target.Interior.Color = vbGreen
                MsgBox "Hello"
        End If
    End Select
End Sub
 
Upvote 0
In every Sheet module
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    Ola
End Sub

Copy the 135 rows code from Post #1 into a regular module but name it Sub Ola() instead of the Private Sub Worksheet_Change
 
Upvote 0
My Aswer Is This said:
There is a lot of code here:
But here is a simple example use select case

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on any sheet tab. Not in every sheet tab. Just do it once
Select View Code from the pop-up context menu
Double click on "ThisWorkbook"
Paste the code in the VBA edit window
In my code it will set the cell color to green when you make any change to any value in column 1 of sheets 13 to42

But paste in your code:
VBA Code: 
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'Modified  8/27/2022  10:18:11 PM  EDT

    Select Case ActiveSheet.Index
        Case 13 To 42
            If Target.Column = 1 Then
                Target.Interior.Color = vbGreen
                MsgBox "Hello"
        End If
    End Select
End Sub
Click to expand...
There is absolutely a lot of code here! But I need each instance to designate by turning color. Sigh... Once again thanks for the help.
I tried this code and for some reason it won't let me designate the Case Range("H3").Value to Case Range Worksheet('Tally Sheet').Range("H3").Value
 
Upvote 0
jolivanes said:
In every Sheet module
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    Ola
End Sub

Copy the 135 rows code from Post #1 into a regular module but name it Sub Ola() instead of the Private Sub Worksheet_Change
Click to expand...
I tried this but how do I let it know onl to activate on sheets 13 through 42?
Rich (BB code): 
Private Sub Ola()
Ola
Dim cell As Range
Dim Rng1 As Range
 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 Worksheet("Tally Sheet").Range("Z3").Value
            cell.Interior.ColorIndex = 6
            cell.Font.Bold = True
            cell.Font.ColorIndex = 1
        Case Worksheet("Tally Sheet").Range("Z4").Value
            cell.Interior.ColorIndex = 43
            cell.Font.Bold = True
            cell.Font.ColorIndex = 1
        Case Worksheet("Tally Sheet").Range("Z5").Value
            cell.Interior.ColorIndex = 54
            cell.Font.Bold = True
            cell.Font.ColorIndex = 2
        Case Worksheet("Tally Sheet").Range("Z6").Value
            cell.Interior.ColorIndex = 39
            cell.Font.Bold = True
            cell.Font.ColorIndex = 1
        Case Worksheet("Tally Sheet").Range("Z7").Value
            cell.Interior.ColorIndex = 2
            cell.Font.Bold = True
            cell.Font.ColorIndex = 1
        Case Worksheet("Tally Sheet").Range("Z8").Value
            cell.Interior.ColorIndex = 30
            cell.Font.Bold = True
            cell.Font.ColorIndex = 2
        Case Worksheet("Tally Sheet").Range("Z9").Value
            cell.Interior.ColorIndex = 39
            cell.Font.Bold = True
            cell.Font.ColorIndex = 1
        Case Worksheet("Tally Sheet").Range("Z10").Value
            cell.Interior.ColorIndex = 26
            cell.Font.Bold = True
            cell.Font.ColorIndex = 1
        Case Worksheet("Tally Sheet").Range("Z11").Value
            cell.Interior.ColorIndex = 46
            cell.Font.Bold = True
            cell.Font.ColorIndex = 1
        Case Worksheet("Tally Sheet").Range("Z12").Value
            cell.Interior.ColorIndex = 3
            cell.Font.Bold = True
            cell.Font.ColorIndex = 2
        End Select
    Next
End Sub
 
Upvote 0
Sorry, my bad.
I should have said to put this in each of the Sheet Modules that you want to address. (13 to 42)
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    Ola
End Sub
BTW, Change "Ola" in both to something descriptive.
 
Upvote 0
My Aswer Is This said:
Did you try my script I posted in Post #2 ?
Click to expand...
I did but I'M still having issues. This is what I'm trying to do. As you see here I'm using the above code to change the color of the cell on input. You see "a" change the color to yellow. The "a" is input on the Tally Sheet cell Z3 and is reflected on sheets 13 through 42. Right now I'm using the above code on every sheet 13 through 42.
 

Attachments

  • Screenshot (4).png
    Screenshot (4).png
    39.9 KB · Views: 7
Upvote 0
My code would perform the same task on sheets 13 to 42
As I thought you wanted. There is way too much code here for me to read to understand what your code is attempting to do.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Hiding lay-out with a macro
Replies
2
Views
357
MIBO
M
M
  • Question Question
Prevent worksheet change from triggering when similar macro is executed?
Replies
1
Views
142
AlphaFrog
AlphaFrog
B
  • Solved
Worksheet Change - multiple Loops in one sub
2
Replies
15
Views
824
mumps
mumps
Noid67
  • Question Question
Multiple worksheet change and Formula use
2
Replies
17
Views
721
mumps
mumps
A
  • Question Question
I can't get private sub to run to update sheet name when the value in Cell A1 has changed
Replies
1
Views
511
duggie33
D

Forum statistics

Threads
1,224,823
Messages
6,181,177
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top