I am trying to auto color cells using this VB code.



Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer

If Not Intersect(Target, Range("A1:A100")) Is Nothing Then
Select Case Target
Case 1
icolor = 6
Case 2
icolor = 12
Case 3
icolor = 7
Case 4
icolor = 53
Case 5
icolor = 15
Case 6
icolor = 42
Case 7
icolor = 1
Case 8
icolor = 20
Case 9
icolor = 30
Case 10
icolor = 40
Case 11
icolor = 51
Case 12
icolor = 14

Case Else
'Whatever
End Select

Target.Interior.ColorIndex = icolor
End If

End Sub

The problem I am having is I have to either hard code in data to change the
color of the cell or I can only copy data into cells one cell at a time to
change the color. What I would like to do is copy data that is on multiple
rows and past it to the work sheet with the VB code and have all of those
cells auto color to my specified colors. I am getting a Debug error stating
"Run time error '13' Type Mismatch" Any help will be greatly appreciated.

Try changing your Worksheet_Change code to:


Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A100")) Is Nothing Then
ColorMultiCells
End If
End Sub

Then add the following sub to a standard Module:

Option Explicit

Sub ColorMultiCells()

Dim icolor As Integer
Dim oCell As Range

For Each oCell In Selection
Select Case oCell.Value
Case 1
icolor = 6
Case 2
icolor = 12
Case 3
icolor = 7
Case 4
icolor = 53
Case 5
icolor = 15
Case 6
icolor = 42
Case 7
icolor = 1
Case 8
icolor = 20
Case 9
icolor = 30
Case 10
icolor = 40
Case 11
icolor = 51
Case 12
icolor = 14

Case Else
'Whatever
End Select

oCell.Interior.ColorIndex = icolor

Next oCell
End Sub




"TNfisherman" wrote:

I am trying to auto color cells using this VB code.



Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer

If Not Intersect(Target, Range("A1:A100")) Is Nothing Then
Select Case Target
Case 1
icolor = 6
Case 2
icolor = 12
Case 3
icolor = 7
Case 4
icolor = 53
Case 5
icolor = 15
Case 6
icolor = 42
Case 7
icolor = 1
Case 8
icolor = 20
Case 9
icolor = 30
Case 10
icolor = 40
Case 11
icolor = 51
Case 12
icolor = 14

Case Else
'Whatever
End Select

Target.Interior.ColorIndex = icolor
End If

End Sub

The problem I am having is I have to either hard code in data to change the
color of the cell or I can only copy data into cells one cell at a time to
change the color. What I would like to do is copy data that is on multiple
rows and past it to the work sheet with the VB code and have all of those
cells auto color to my specified colors. I am getting a Debug error stating
"Run time error '13' Type Mismatch" Any help will be greatly appreciated.