Worksheet Change on multiple sheets

imback2nite

imback2nite

Board Regular
Joined
Oct 30, 2004
Messages
203
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

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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
Hiding lay-out with a macro
Replies
2
Views
249
MIBO
M
B
  • Solved
Worksheet Change - multiple Loops in one sub
2
Replies
15
Views
360
mumps
mumps
Noid67
  • Question
Multiple worksheet change and Formula use
2
Replies
17
Views
407
mumps
mumps
A
  • Question
I can't get private sub to run to update sheet name when the value in Cell A1 has changed
Replies
1
Views
342
duggie33
D
X
  • Question
Locking/unlocking a cell with a Combo Box value Mouse click problem
Replies
0
Views
506
XrayLemi
X

Forum statistics

Threads
1,215,762
Messages
6,126,736
Members
449,334
Latest member
moses007

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