Auto Cell Color using VB
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. |
Auto Cell Color using VB
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. |
All times are GMT +1. The time now is 04:34 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com