Problem Code!

MarkDave

New Member
Joined
Mar 25, 2009
Messages
30
Office Version
  1. 365
Platform
  1. Windows
I've the following code which I use to colour cells when a certain word is inputted.

There is a need to cut and paste or move this info between cells, however when I do so it comes up with an error, (although if I click end it ignores it and works)...any ideas how I can amend?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
If Not Intersect(Target, Range("B5:U2082")) Is Nothing Then
Select Case Target
Case "trial"
icolor = 7

Case "Trial"
icolor = 7
Case "tc"
icolor = 28

Case "TC"
icolor = 28
Case "hols"
icolor = 3

Case "Hols"
icolor = 3
Case "sit"
icolor = 4

Case "Sit"
icolor = 4
Case "al"
icolor = 6

Case "AL"
icolor = 6
Case "a/l"
icolor = 6

Case "A/L"
icolor = 6

Case "jmt"
icolor = 39

Case "JMT"
icolor = 39
Case Else
'Whatever
End Select

Target.Interior.ColorIndex = icolor

End If

End Sub
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
I assume it would be from copying and pasting more than one cell and then the target is more than 1 cell, so it cannot look at that value. You could do one of 2 things, see if this will work for you, if not post back and we can try another approach:

Rich (BB code):
Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
If Not Intersect(Target, Range("B5:U2082")) Is Nothing and target.count = 1 Then
Select Case Target
Case "trial"
icolor = 7

Case "Trial"
icolor = 7
Case "tc"
icolor = 28

Case "TC"
icolor = 28
Case "hols"
icolor = 3

Case "Hols"
icolor = 3
Case "sit"
icolor = 4

Case "Sit"
icolor = 4
Case "al"
icolor = 6

Case "AL"
icolor = 6
Case "a/l"
icolor = 6

Case "A/L"
icolor = 6

Case "jmt"
icolor = 39

Case "JMT"
icolor = 39
Case Else
'Whatever
End Select

Target.Interior.ColorIndex = icolor

End If

End Sub
 
Upvote 0
Don't know what you changed, but it worked a treat!

Thanks very much!
 
Upvote 0
I added the code in bold that made sure there was only one cell entered into otherwise it wouldn't runt eh coloring part of the code. So this will not work if you paste trial in 3 cells, not sure if it needs to work in that instance or if that would ever happen?
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

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