M
marcello121
Hi,
I have a function to set up the color of a cell based on the value input by
the user. It does work perfectly when a user enters a value, but it fails
when the user copy a value in a cell and paste it over more than one cell at
a time.
function:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target = "H" Then
Target.Interior.ColorIndex = 4
End If
If Target = "h" Then
Target.Interior.ColorIndex = 43
End If
If Target = "S" Then
Target.Interior.ColorIndex = 27
End If
If Target = "s" Then
Target.Interior.ColorIndex = 36
End If
If Target = "t" Then
Target.Interior.ColorIndex = 45
End If
If Target = "T" Then
Target.Interior.ColorIndex = 46
End If
End Sub
Error message:Type mismatch, located at If Target = "H" Then.
I can t use conditional formatting because I want to extend this function to
other topics (sum, ...).
Is there a way to make this function working with copy paste over more 1
cell ?
Rds
Marcello
I have a function to set up the color of a cell based on the value input by
the user. It does work perfectly when a user enters a value, but it fails
when the user copy a value in a cell and paste it over more than one cell at
a time.
function:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target = "H" Then
Target.Interior.ColorIndex = 4
End If
If Target = "h" Then
Target.Interior.ColorIndex = 43
End If
If Target = "S" Then
Target.Interior.ColorIndex = 27
End If
If Target = "s" Then
Target.Interior.ColorIndex = 36
End If
If Target = "t" Then
Target.Interior.ColorIndex = 45
End If
If Target = "T" Then
Target.Interior.ColorIndex = 46
End If
End Sub
Error message:Type mismatch, located at If Target = "H" Then.
I can t use conditional formatting because I want to extend this function to
other topics (sum, ...).
Is there a way to make this function working with copy paste over more 1
cell ?
Rds
Marcello