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
 
Sort by date Sort by votes
My Aswer Is This said:
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.
Click to expand...
Thank You! I went with the code below. It even knocked half a megabyte off the workbook!
What I was trying to do was for an event that, say referenced Worksheets("Tally Sheet").Range("Z3") then the cell would turn a specific color. Problem was I have 24 specific colors and fonts! Putting this code in "ThisWorkbook" saved be from putting the original code on every sheet!
Once again, Thank you very much!

Rich (BB code): 
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Select Case ActiveSheet.Index
        Case 14 To 44
            If Target = Worksheets("Tally Sheet").Range("Z3") Then
                Target.Interior.ColorIndex = 6
                Target.Font.Bold = True
                Target.Font.ColorIndex = 1
            ElseIf Target = Worksheets("Tally Sheet").Range("Z4") Then
                Target.Interior.ColorIndex = 43
                Target.Font.Bold = True
                Target.Font.ColorIndex = 1
            ElseIf Target = Worksheets("Tally Sheet").Range("Z5") Then
                Target.Interior.ColorIndex = 54
                Target.Font.Bold = True
                Target.Font.ColorIndex = 2
            ElseIf Target = Worksheets("Tally Sheet").Range("Z6") Then
                Target.Interior.ColorIndex = 39
                Target.Font.Bold = True
                Target.Font.ColorIndex = 1
            ElseIf Target = Worksheets("Tally Sheet").Range("Z7") Then
                Target.Interior.ColorIndex = 2
                Target.Font.Bold = True
                Target.Font.ColorIndex = 1
            ElseIf Target = Worksheets("Tally Sheet").Range("Z8") Then
                Target.Interior.ColorIndex = 30
                Target.Font.Bold = True
                Target.Font.ColorIndex = 2
            ElseIf Target = Worksheets("Tally Sheet").Range("Z9") Then
                Target.Interior.ColorIndex = 39
                Target.Font.Bold = True
                Target.Font.ColorIndex = 1
            ElseIf Target = Worksheets("Tally Sheet").Range("Z10") Then
                Target.Interior.ColorIndex = 26
                Target.Font.Bold = True
                Target.Font.ColorIndex = 1
            ElseIf Target = Worksheets("Tally Sheet").Range("Z11") Then
                Target.Interior.ColorIndex = 46
                Target.Font.Bold = True
                Target.Font.ColorIndex = 1
            ElseIf Target = Worksheets("Tally Sheet").Range("Z12") Then
                Target.Interior.ColorIndex = 3
                Target.Font.Bold = True
                Target.Font.ColorIndex = 2
            ElseIf Target = Worksheets("Tally Sheet").Range("Z13") Then
                Target.Interior.ColorIndex = 53
                Target.Font.Bold = True
                Target.Font.ColorIndex = 2
            ElseIf Target = Worksheets("Tally Sheet").Range("Z14") Then
                Target.Interior.ColorIndex = 29
                Target.Font.Bold = True
                Target.Font.ColorIndex = 2
            ElseIf Target = Worksheets("Tally Sheet").Range("Z15") Then
                Target.Interior.ColorIndex = 40
                Target.Font.Bold = True
                Target.Font.ColorIndex = 1
            ElseIf Target = Worksheets("Tally Sheet").Range("Z16") Then
                Target.Interior.ColorIndex = 10
                Target.Font.Bold = True
                Target.Font.ColorIndex = 2
            ElseIf Target = Worksheets("Tally Sheet").Range("Z17") Then
                Target.Interior.ColorIndex = 35
                Target.Font.Bold = True
                Target.Font.ColorIndex = 1
            ElseIf Target = Worksheets("Tally Sheet").Range("Z18") Then
                Target.Interior.ColorIndex = 10
                Target.Font.Bold = True
                Target.Font.ColorIndex = 2
            ElseIf Target = Worksheets("Tally Sheet").Range("Z19") Then
                Target.Interior.ColorIndex = 5
                Target.Font.Bold = True
                Target.Font.ColorIndex = 2
            ElseIf Target = Worksheets("Tally Sheet").Range("Z20") Then
                Target.Interior.ColorIndex = 16
                Target.Font.Bold = True
                Target.Font.ColorIndex = 1
            ElseIf Target = Worksheets("Tally Sheet").Range("Z21") Then
                Target.Interior.ColorIndex = 42
                Target.Font.Bold = True
                Target.Font.ColorIndex = 1
            ElseIf Target = Worksheets("Tally Sheet").Range("Z22") Then
                Target.Interior.ColorIndex = 4
                Target.Font.Bold = True
                Target.Font.ColorIndex = 1
            ElseIf Target = Worksheets("Tally Sheet").Range("Z23") Then
                Target.Interior.ColorIndex = 46
                Target.Font.Bold = True
                Target.Font.ColorIndex = 2
            ElseIf Target = Worksheets("Tally Sheet").Range("Z24") Then
                Target.Interior.ColorIndex = 34
                Target.Font.Bold = True
                Target.Font.ColorIndex = 1
            ElseIf Target = Worksheets("Tally Sheet").Range("Z25") Then
                Target.Interior.ColorIndex = 38
                Target.Font.Bold = True
                Target.Font.ColorIndex = 1
            ElseIf Target = Worksheets("Tally Sheet").Range("Z26") Then
                Target.Interior.ColorIndex = 8
                Target.Font.Bold = True
                Target.Font.ColorIndex = 1
            ElseIf Target = Worksheets("Tally Sheet").Range("Z27") Then
                Target.Interior.ColorIndex = 47
                Target.Font.Bold = True
                Target.Font.ColorIndex = 2
            ElseIf Target = Worksheets("Tally Sheet").Range("Z28") Then
                Target.Interior.ColorIndex = 12
                Target.Font.Bold = True
                Target.Font.ColorIndex = 2
            ElseIf Target = Worksheets("Tally Sheet").Range("Z29") Then
                Target.Interior.ColorIndex = 33
                Target.Font.Bold = True
                Target.Font.ColorIndex = 1
            ElseIf Target = Worksheets("Tally Sheet").Range("Z30") Then
                Target.Interior.ColorIndex = 53
                Target.Font.Bold = True
                Target.Font.ColorIndex = 2
            ElseIf Target = Worksheets("Tally Sheet").Range("Z31") Then
                Target.Interior.ColorIndex = xlNone
                Target.Font.Bold = False
        End If
    End Select
End Sub
 
Upvote 0

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
imback2nite said:
Thank You! I went with the code below. It even knocked half a megabyte off the workbook!
What I was trying to do was for an event that, say referenced Worksheets("Tally Sheet").Range("Z3") then the cell would turn a specific color. Problem was I have 24 specific colors and fonts! Putting this code in "ThisWorkbook" saved be from putting the original code on every sheet!
Once again, Thank you very much!

Rich (BB code): 
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Select Case ActiveSheet.Index
        Case 14 To 44
            If Target = Worksheets("Tally Sheet").Range("Z3") Then
                Target.Interior.ColorIndex = 6
                Target.Font.Bold = True
                Target.Font.ColorIndex = 1
            ElseIf Target = Worksheets("Tally Sheet").Range("Z4") Then
                Target.Interior.ColorIndex = 43
                Target.Font.Bold = True
                Target.Font.ColorIndex = 1
            ElseIf Target = Worksheets("Tally Sheet").Range("Z5") Then
                Target.Interior.ColorIndex = 54
                Target.Font.Bold = True
                Target.Font.ColorIndex = 2
            ElseIf Target = Worksheets("Tally Sheet").Range("Z6") Then
                Target.Interior.ColorIndex = 39
                Target.Font.Bold = True
                Target.Font.ColorIndex = 1
            ElseIf Target = Worksheets("Tally Sheet").Range("Z7") Then
                Target.Interior.ColorIndex = 2
                Target.Font.Bold = True
                Target.Font.ColorIndex = 1
            ElseIf Target = Worksheets("Tally Sheet").Range("Z8") Then
                Target.Interior.ColorIndex = 30
                Target.Font.Bold = True
                Target.Font.ColorIndex = 2
            ElseIf Target = Worksheets("Tally Sheet").Range("Z9") Then
                Target.Interior.ColorIndex = 39
                Target.Font.Bold = True
                Target.Font.ColorIndex = 1
            ElseIf Target = Worksheets("Tally Sheet").Range("Z10") Then
                Target.Interior.ColorIndex = 26
                Target.Font.Bold = True
                Target.Font.ColorIndex = 1
            ElseIf Target = Worksheets("Tally Sheet").Range("Z11") Then
                Target.Interior.ColorIndex = 46
                Target.Font.Bold = True
                Target.Font.ColorIndex = 1
            ElseIf Target = Worksheets("Tally Sheet").Range("Z12") Then
                Target.Interior.ColorIndex = 3
                Target.Font.Bold = True
                Target.Font.ColorIndex = 2
            ElseIf Target = Worksheets("Tally Sheet").Range("Z13") Then
                Target.Interior.ColorIndex = 53
                Target.Font.Bold = True
                Target.Font.ColorIndex = 2
            ElseIf Target = Worksheets("Tally Sheet").Range("Z14") Then
                Target.Interior.ColorIndex = 29
                Target.Font.Bold = True
                Target.Font.ColorIndex = 2
            ElseIf Target = Worksheets("Tally Sheet").Range("Z15") Then
                Target.Interior.ColorIndex = 40
                Target.Font.Bold = True
                Target.Font.ColorIndex = 1
            ElseIf Target = Worksheets("Tally Sheet").Range("Z16") Then
                Target.Interior.ColorIndex = 10
                Target.Font.Bold = True
                Target.Font.ColorIndex = 2
            ElseIf Target = Worksheets("Tally Sheet").Range("Z17") Then
                Target.Interior.ColorIndex = 35
                Target.Font.Bold = True
                Target.Font.ColorIndex = 1
            ElseIf Target = Worksheets("Tally Sheet").Range("Z18") Then
                Target.Interior.ColorIndex = 10
                Target.Font.Bold = True
                Target.Font.ColorIndex = 2
            ElseIf Target = Worksheets("Tally Sheet").Range("Z19") Then
                Target.Interior.ColorIndex = 5
                Target.Font.Bold = True
                Target.Font.ColorIndex = 2
            ElseIf Target = Worksheets("Tally Sheet").Range("Z20") Then
                Target.Interior.ColorIndex = 16
                Target.Font.Bold = True
                Target.Font.ColorIndex = 1
            ElseIf Target = Worksheets("Tally Sheet").Range("Z21") Then
                Target.Interior.ColorIndex = 42
                Target.Font.Bold = True
                Target.Font.ColorIndex = 1
            ElseIf Target = Worksheets("Tally Sheet").Range("Z22") Then
                Target.Interior.ColorIndex = 4
                Target.Font.Bold = True
                Target.Font.ColorIndex = 1
            ElseIf Target = Worksheets("Tally Sheet").Range("Z23") Then
                Target.Interior.ColorIndex = 46
                Target.Font.Bold = True
                Target.Font.ColorIndex = 2
            ElseIf Target = Worksheets("Tally Sheet").Range("Z24") Then
                Target.Interior.ColorIndex = 34
                Target.Font.Bold = True
                Target.Font.ColorIndex = 1
            ElseIf Target = Worksheets("Tally Sheet").Range("Z25") Then
                Target.Interior.ColorIndex = 38
                Target.Font.Bold = True
                Target.Font.ColorIndex = 1
            ElseIf Target = Worksheets("Tally Sheet").Range("Z26") Then
                Target.Interior.ColorIndex = 8
                Target.Font.Bold = True
                Target.Font.ColorIndex = 1
            ElseIf Target = Worksheets("Tally Sheet").Range("Z27") Then
                Target.Interior.ColorIndex = 47
                Target.Font.Bold = True
                Target.Font.ColorIndex = 2
            ElseIf Target = Worksheets("Tally Sheet").Range("Z28") Then
                Target.Interior.ColorIndex = 12
                Target.Font.Bold = True
                Target.Font.ColorIndex = 2
            ElseIf Target = Worksheets("Tally Sheet").Range("Z29") Then
                Target.Interior.ColorIndex = 33
                Target.Font.Bold = True
                Target.Font.ColorIndex = 1
            ElseIf Target = Worksheets("Tally Sheet").Range("Z30") Then
                Target.Interior.ColorIndex = 53
                Target.Font.Bold = True
                Target.Font.ColorIndex = 2
            ElseIf Target = Worksheets("Tally Sheet").Range("Z31") Then
                Target.Interior.ColorIndex = xlNone
                Target.Font.Bold = False
        End If
    End Select
End Sub
Click to expand...
Yes this looks like the code I suggested with your code installed in it. Glad I was able to help you
 
Upvote 0
Shorter and neater version:
1) There is only one sheet is focused, then no need to select case. This is enough:
VBA Code: 
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.Name <> "Tally Sheet" Or Intersect(Target, Sh.Range("Z3:Z31")) Is Nothing Or Target.Count > 1 Then Exit Sub
2) Also: Index list of interior and font should be stored in 2 arrays like this:
VBA Code: 
Inter = Array(6, 43, 54, 39, 2, 30, 39, 26, 46, 3, 53, 29, 40, _
        10, 35, 10, 5, 16, 42, 4, 46, 34, 39, 8, 47, 12, 33, 53, xlNone)
Fon = Array(1, 1, 2, 1, 2, 2, 1, 1, 1, 2, 2, 2, 1, 2, 1, 2, 2, _
        1, 1, 1, 2, 1, 1, 1, 2, 2, 1, 2, 0)
With items order are same order with range "Z3:Z31"

Complete code:
VBA Code: 
Option Explicit
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.Name <> "Tally Sheet" Or Intersect(Target, Sh.Range("Z3:Z31")) Is Nothing Or Target.Count > 1 Then Exit Sub
Dim Inter, Fon
Inter = Array(6, 43, 54, 39, 2, 30, 39, 26, 46, 3, 53, 29, 40, _
        10, 35, 10, 5, 16, 42, 4, 46, 34, 39, 8, 47, 12, 33, 53, xlNone)
Fon = Array(1, 1, 2, 1, 2, 2, 1, 1, 1, 2, 2, 2, 1, 2, 1, 2, 2, _
        1, 1, 1, 2, 1, 1, 1, 2, 2, 1, 2, 0)
With Target
    .Interior.ColorIndex = Inter(.Row - 3)
    .Font.ColorIndex = Fon(.Row - 3)
    .Font.Bold = IIf(.Row = 31, 0, 1)
End With
End Sub
 
Upvote 0
If you want to avoid all those Elseif:

Try this:

See I entered a ":"
Thats a :
Between tasks

VBA Code: 
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    'Modified  8/30/2022  10:32:06 PM  EDT

    Select Case ActiveSheet.Index
        Case 14 To 44
            If Target = Worksheets("Tally Sheet").Range("Z3") Then: Target.Interior.ColorIndex = 6: Target.Font.Bold = True: Target.Font.ColorIndex = 1
            If Target = Worksheets("Tally Sheet").Range("Z4") Then: Target.Interior.ColorIndex = 43: Target.Font.Bold = True: Target.Font.ColorIndex = 1
            
    End Select
End Sub
 
Upvote 0
New problem, Sigh... When I try to delete a row of information I get an error 13 code. Type mismatch. When I use the original code I'm able to delete entire rows and columns. For example, if I try to delete A1:A8 information using the original code, it deletes without any errors. When I use the second code I get the mismatch error, the information but the color format will not disappear.

Original Code

Rich (BB 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 Else
            cell.Interior.ColorIndex = xlNone
            cell.Font.Bold = False
        End Select
    Next
End Sub

New Code

Rich (BB code): 
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    'Modified  8/30/2022  10:32:06 PM  EDT

    Select Case ActiveSheet.Index
        Case 14 To 44
            If Target = Worksheets("Tally Sheet").Range("Z3") Then: Target.Interior.ColorIndex = 6: Target.Font.Bold = True: Target.Font.ColorIndex = 1
            If Target = Worksheets("Tally Sheet").Range("Z4") Then: Target.Interior.ColorIndex = 43: Target.Font.Bold = True: Target.Font.ColorIndex = 1
            If Target = Worksheets("Tally Sheet").Range("Z5") Then: Target.Interior.ColorIndex = 54: Target.Font.Bold = True: Target.Font.ColorIndex = 2
    End Select
End Sub
 
Upvote 0
Try this:
VBA Code: 
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'Modified  9/1/2022  2:23:41 AM  EDT
    
    Select Case ActiveSheet.Index
        Case 14 To 44
       If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
    
             If Target = Worksheets("Tally Sheet").Range("Z3") Then: Target.Interior.ColorIndex = 6: Target.Font.Bold = True: Target.Font.ColorIndex = 1
            If Target = Worksheets("Tally Sheet").Range("Z4") Then: Target.Interior.ColorIndex = 43: Target.Font.Bold = True: Target.Font.ColorIndex = 1
            If Target = Worksheets("Tally Sheet").Range("Z5") Then: Target.Interior.ColorIndex = 54: Target.Font.Bold = True: Target.Font.ColorIndex = 2
    End Select
End Sub
 
Upvote 0
My Aswer Is This said:
Try this:
VBA Code: 
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'Modified  9/1/2022  2:23:41 AM  EDT
   
    Select Case ActiveSheet.Index
        Case 14 To 44
       If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
   
             If Target = Worksheets("Tally Sheet").Range("Z3") Then: Target.Interior.ColorIndex = 6: Target.Font.Bold = True: Target.Font.ColorIndex = 1
            If Target = Worksheets("Tally Sheet").Range("Z4") Then: Target.Interior.ColorIndex = 43: Target.Font.Bold = True: Target.Font.ColorIndex = 1
            If Target = Worksheets("Tally Sheet").Range("Z5") Then: Target.Interior.ColorIndex = 54: Target.Font.Bold = True: Target.Font.ColorIndex = 2
    End Select
End Sub
Click to expand...
Now I'm getting Run-time error '438' Object doesn't support this property of method
When I select and delete the range the information does in fact delete but the color formatting remains and I get this error message. Then I debug and it highlights the
Rich (BB code): 
If Target.Cells.CountLarge > 1 Or IsEmpty(Target)
 If it's not one thing, it's another. :sneaky:
I appreciate the help!
 
Upvote 0
What version of Excel are you using?
In your profile it shows 2003 or older which is not very specific.
If your using 2003 that is very very old.
 
Upvote 0
My Aswer Is This said:
What version of Excel are you using?
In your profile it shows 2003 or older which is not very specific.
If your using 2003 that is very very old.
Click to expand...
I'm using 2003. Yes, it is old. Problem being is that Excel 2003, although limited, is the compatible version I'll be having to use.
 
Upvote 0
imback2nite said:
I'm using 2003. Yes, it is old. Problem being is that Excel 2003, although limited, is the compatible version I'll be having to use.
Click to expand...
If you're using 2003 just use Count rather than CountLarge as CountLarge didn't exist then (didn't need to with the number of cells in 2003)
 
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,178
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