Auto refresh for VB script to take effect.

G

Guest

I have the following VB script which changes the color of the cell based on
its
value.
When the value changes, the color doesn't change at the moment. I need to
double click on the cell, for the color to change.
How can i make it refresh automatically? Is there a problem with the code?

This is my code.

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A1:AQ120")) Is Nothing Then
Select Case Target

Case Is = "A"
icolor = 38
Case Is = "B"
icolor = 38
Case Is = "C"
icolor = 35
Case Is = "D"
icolor = 36
Case Is = "E"
icolor = 39
Case Is = "F"
icolor = 35
Case Is = "G"
icolor = 37
Case Is = "H"
icolor = 34
Case Is = "I"
icolor = 40
Case Is = "J"
icolor = 40
Case Is = "K"
icolor = 34
Case Is = "L"
icolor = 34
Case Is = "M"
icolor = 34
Case Else
'Whatever
End Select

Target.Interior.ColorIndex = icolor
End If

End Sub
 
C

Carim

In a separate macro, just to reinitiate your excel ...

As a matter of fact, your macro has always worked ... but an incident
has very recently occured, while debuging another macro... for example
.... it is this incident which has interrupted the EnableEvents mode ...

HTH
Carim
 
W

WhytheQ

A good place to put that line is in an auto exec macro like:

'========================
Sub Auto_Open()
Application.EnableEvents = True
End Sub
========================

....the above will fire when the workbook is opened on any other user's
machine.
Rgds
J
 
G

Guest

I did a real stupid code. Just recorded a macro for double clicking the cells
with look-up. And then copied the code for other cells. Now i run this macro
to refresh.
IS there a easier or smaller code to replace this. This code is really STUPID.

Sub Refresh()
'
' Refresh Macro
' Macro recorded 13/10/2006 by XXX
'

'
Range("C6").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(R[-3]C,'Desk Allocation'!R2C3:R130C6,2,FALSE)"
Range("C11").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(R[-3]C,'Desk Allocation'!R2C3:R130C6,2,FALSE)"
Range("C1124").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(R[-3]C,'Desk Allocation'!R2C3:R130C6,2,FALSE)"
Range("C29").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(R[-3]C,'Desk Allocation'!R2C3:R130C6,2,FALSE)"

End Sub
 
T

Tom Ogilvy

Sub Refresh()

Range("C6,C11,C1124,C29").FormulaR1C1 = _
"=VLOOKUP(R[-3]C,'Desk Allocation'!R2C3:R130C6,2,FALSE)"
End Sub

--
Regards,
Tom Ogilvy


Rajula said:
I did a real stupid code. Just recorded a macro for double clicking the
cells
with look-up. And then copied the code for other cells. Now i run this
macro
to refresh.
IS there a easier or smaller code to replace this. This code is really
STUPID.

Sub Refresh()
'
' Refresh Macro
' Macro recorded 13/10/2006 by XXX
'

'
Range("C6").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(R[-3]C,'Desk Allocation'!R2C3:R130C6,2,FALSE)"
Range("C11").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(R[-3]C,'Desk Allocation'!R2C3:R130C6,2,FALSE)"
Range("C1124").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(R[-3]C,'Desk Allocation'!R2C3:R130C6,2,FALSE)"
Range("C29").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(R[-3]C,'Desk Allocation'!R2C3:R130C6,2,FALSE)"

End Sub

Carim said:
Close Excel and re-open it ...

Carim
Click to expand...
Click to expand...
 
G

Guest

I have around 200 cells in the range, not just the 4.
Any other solution.

Tom Ogilvy said:
Sub Refresh()

Range("C6,C11,C1124,C29").FormulaR1C1 = _
"=VLOOKUP(R[-3]C,'Desk Allocation'!R2C3:R130C6,2,FALSE)"
End Sub

--
Regards,
Tom Ogilvy


Rajula said:
I did a real stupid code. Just recorded a macro for double clicking the
cells
with look-up. And then copied the code for other cells. Now i run this
macro
to refresh.
IS there a easier or smaller code to replace this. This code is really
STUPID.

Sub Refresh()
'
' Refresh Macro
' Macro recorded 13/10/2006 by XXX
'

'
Range("C6").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(R[-3]C,'Desk Allocation'!R2C3:R130C6,2,FALSE)"
Range("C11").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(R[-3]C,'Desk Allocation'!R2C3:R130C6,2,FALSE)"
Range("C1124").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(R[-3]C,'Desk Allocation'!R2C3:R130C6,2,FALSE)"
Range("C29").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(R[-3]C,'Desk Allocation'!R2C3:R130C6,2,FALSE)"

End Sub

Carim said:
Close Excel and re-open it ...

Carim
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

conditional formating 1
Conditional Formatting- Refresh 7
Subject - Loop/Conditional formatting 3 cells above. - more than 3 1
Loop/Conditional formatting 3 cells above - More than 3 conditions 4
Pass Cell Value & Formatting to Another Worksheet 1
VBA Code to run when any cell is updated 0
Case statement comparing two ranges 4
Run Time Error 13- Type mismatch 4

Top