Conditional formatting...

[gjsaturday]

I have the following code on the tab of an excel worksheet.

Sub change_cell_color(ByVal Target As Range)
Dim icolor As Integer
Worksheets("sheet1").Activate
areaCount = Selection.Rows.Count
If areaCount = 1 Then
If Not Intersect(Target, Range("C2:AH85")) Is Nothing Then

Select Case Target
Case 1
icolor = 50
Case 2
icolor = 6
Case 3
icolor = 54
Case 4
icolor = 41
Case 5
icolor = 3
Case Else
icolor = 0
End Select

Target.Interior.ColorIndex = icolor
End If
End If

End Sub

When I cut and paste from another workbook, the cells do not change to the color I want, as opposed to typing in the worksheet with the code above. Can somebody help me?

 

[xlbo]

to be honest - I don't know how that code is working automatically - it is not in the appropriate change event - it needs to be in the worksheet CHANGE event to fire automatically

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question