PDA

View Full Version : Excel VBA Copy & Paste


Mad_Kitty
06-24-2008, 01:38 AM
I have been asked to update a spreadsheet that has been distributed around a number of people, who have filled in there own information. They may be using different versions of Excel but for my purposes I am using Excel 2003.

What they want is for the background colour of the cell to be changed depending on the value of the cell, for this purpose I have written the code and it works ok provided new numbers are entered. It does not work unless you enter a number and press enter for example if you copy and paste the cells all change to white.

Can any one point me in the right direction.

Thanks, the code I have is as follows.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
On Error Resume Next
Application.DisplayAlerts = False
If Not Intersect(Target, Range("E17:P51")) Is Nothing Then
Select Case Target
Case 0 To 0
[White]
Case 1 To 75
icolor = 3
Case 76 To 95
icolor = 26
Case 96 To 100
icolor = 45
Case 101 To 102
icolor = 46
Case 103 To 105
icolor = 4
Case 106 To 1000
icolor = 50
Case Else
'Whatever
End Select

Target.Interior.ColorIndex = icolor
End If
End Sub
Bob Phillips
06-24-2008, 02:12 AM
Private Sub Worksheet_Calculate()
Dim Target As Range
Dim icolor As Long
On Error Resume Next
Application.DisplayAlerts = False
For Each Target In Me.Range("E17:P51")
icolor = xlColorIndexNone
Select Case Target
Case 0 To 0: [White]
Case 1 To 75: icolor = 3
Case 76 To 95: icolor = 26
Case 96 To 100: icolor = 45
Case 101 To 102: icolor = 46
Case 103 To 105: icolor = 4
Case 106 To 1000: icolor = 50
Case Else: 'Whatever
End Select

Target.Interior.ColorIndex = icolor
Next Target
End Sub
Mad_Kitty
06-24-2008, 02:50 AM
Big thank you
greymalkin
06-24-2008, 11:23 AM
For future reference you don't need VBA to do this, you can just use conditional formatting:

Format - Conditional Formatting

to add as many ranges/formats you need.
Bob Phillips
06-24-2008, 12:34 PM
Not in Excel 2003 and before you cannot, and the OP mentioned 2003.
greymalkin
06-24-2008, 01:34 PM
hmmm..this is strange..I've got 2003 and the conditional formatting option is here for me..perhaps we have some sort of update pack or something?
lucas
06-24-2008, 01:37 PM
conditional formtting in 2003 and before are limited to 3 conditions I believe..
greymalkin
06-24-2008, 01:55 PM
conditional formtting in 2003 and before are limited to 3 conditions I believe..

that is correct. sorry for the bad info :doh: