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

Hi,

Try following to correct :
Application.EnableEvents = True

HTH
Cheers
Carim

Hi,

Where do i need to put this? In the code or as a seperate button??

Regads


"Carim" wrote:

> Hi,
>
> Try following to correct :
> Application.EnableEvents = True
>
> HTH
> Cheers
> 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

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


Carim wrote:
> 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

sorry to say, but nothing seems to be working. Any other smart option?

"WhytheQ" wrote:

> 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
>
>
> Carim wrote:
> > 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
>
>

Close Excel and re-open it ...

Carim

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" wrote:

> Close Excel and re-open it ...
>
> Carim
>
>

Sub Refresh()

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

--
Regards,
Tom Ogilvy


"Rajula" <(E-Mail Removed)> wrote in message
news:A06C777B-E35D-48D3-BEA4-(E-Mail Removed)...
>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" wrote:
>
>> Close Excel and re-open it ...
>>
>> Carim
>>
>>

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

"Tom Ogilvy" wrote:

> Sub Refresh()
>
> Range("C6,C11,C1124,C29").FormulaR1C1 = _
> "=VLOOKUP(R[-3]C,'Desk Allocation'!R2C3:R130C6,2,FALSE)"
> End Sub
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Rajula" <(E-Mail Removed)> wrote in message
> news:A06C777B-E35D-48D3-BEA4-(E-Mail Removed)...
> >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" wrote:
> >
> >> Close Excel and re-open it ...
> >>
> >> Carim
> >>
> >>
>
>
>