Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clear Cells and Worksheet_Change Q
I have the following code that clear cell content with a Range
Sub ClearCells() Application.ScreenUpdating = False Sheets("Log").Activate ActiveSheet.Unprotect Password:="123" Range("D7").Select Selection.ClearContents Application.DisplayFormulaBar = False Range("D7").Select Selection.Copy Range("D7:J30").Select Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _ , Transpose:=False Range("D7").Select ActiveSheet.Protect Password:="123", DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub I also have a Worksheet_Change code that, basically changes a cells colour when data is selected. '----------------------------------------------------------------- Private Sub Worksheet_Change(ByVal Target As Range) '----------------------------------------------------------------- Const WS_RANGE As String = "D7:J30" '<==== change to suit Application.EnableEvents = True If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target Select Case LCase(.Value) Case "Country 1": .Font.ColorIndex = 2 .Interior.ColorIndex = 3 'red Case "Country 2": .Font.ColorIndex = 2 .Interior.ColorIndex = 3 'red Case "Country 3": .Font.ColorIndex = 2 .Interior.ColorIndex = 5 'blue End Select End With End If End Sub My issue is that when I run my ClearContents macro (it clears the cells) but I hit debug within the Worksheet_Change code at line (Type Mismatch):- Select Case LCase(.Value) Why is that and how can I fix? Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clear Cells and Worksheet_Change Q
This code in your ClearCells routine:
Range("D7").Select Selection.Copy Range("D7:J30").Select Selection.PasteSpecial _ Paste:=xlAll, _ Operation:=xlNone, SkipBlanks:=False, _ Transpose:=False 1. You are changing more than one cell at a time, so Target is a multiple-cell range. This is what is producing the Type Mismatch error in the Worksheet_Change event handler. Change your ClearCells routine to set each cell one at the time. 2. In your Worksheet_Change event handler, you are lower-casing the value of the cell in your Select Case statement, then comparing it to a string that has upper-case letters in it (i.e. "Country 1"). You should probably also have a "Case Else" clause after the 3 cases you have listed, in case the value of the Target cell changes back to some other value that needs the cell colors changed back to the original. Case Else .Font.ColorIndex = xlColorIndexAutomatic .Interior.ColorIndex = xlColorIndexNone -- Regards, Bill Renaud |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lock or Unlock Range of Cells on Worksheet_Change Event | Excel Worksheet Functions | |||
Worksheet_Change with discontinuous cells | Excel Programming | |||
worksheet_change event when multiple cells changed (pasted) | Excel Programming | |||
worksheet_change vs. calculate, and worksheet_change not running | Excel Programming |