Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 55
Default macros doesn't execute unless i go into cell and press enter.

HI,
i am very new to Macros and VBA and i the following conditional formatting
ocde in my worsheet:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iSect As Range

Set iSect = Application.Intersect(Range(Target.Address),
Range("BG7:DD60"))

If iSect Is Nothing Or Target.Cells.Count 1 Then
Exit Sub
End If

Application.EnableEvents = False

Select Case Target.Value
Case Is = 0
With Target.Interior
.ColorIndex = xlNone
End With

Case Is < Range("c2")
With Target.Interior
.ColorIndex = 1
End With
With Target.Font
.ColorIndex = 10
End With

Case Is < Range("c3")
With Target.Interior
.ColorIndex = 6
End With
With Target.Font
.ColorIndex = 40
End With

Case Is = "n/a"
With Target.Interior
.ColorIndex = 15
End With

Case Is = "On hold"
With Target.Interior
.ColorIndex = 3
End With

End Select

Application.EnableEvents = True

End Sub

however the cells in the range are popoulated via the following equation:

=IF(OR(INDIRECT(VLOOKUP($B7,REFERENCE!$S$4:$T$56,2 ,FALSE)&"!$C9:$EC9")2),IF(OR(INDIRECT(VLOOKUP($B7 ,REFERENCE!$S$4:$T$56,2,FALSE)&"!$C9:$EC9")="n/a"),"n/a",(MAX(INDIRECT(VLOOKUP($B7,REFERENCE!$S$4:$T$56, 2,FALSE)&"!$C9:$EC9")))),MAX((INDIRECT(VLOOKUP($B7 ,REFERENCE!$S$4:$T$56,2,FALSE)&"!$C9:$EC9"))*(INDI RECT(VLOOKUP($B7,REFERENCE!$S$4:$T$56,2,FALSE)&"!$ C$6:$EC$6"))))

The only way i can seem to get the macros to run is if i go into each cell
and press CNTRL+SHIFT + ENTER ( array formula)
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,939
Default macros doesn't execute unless i go into cell and press enter.

The code that you have requires a change be made to the cell. The value of
the cell changing because the cell recalculates does not count as a change.
Normally you could look at changes in the precident cells of the calculation
but since you are using indirect that it kinda out of the question. But
looking at the code my question to you would be why not just use a regular
built in conditional format?
--
HTH...

Jim Thomlinson


"Emma" wrote:

HI,
i am very new to Macros and VBA and i the following conditional formatting
ocde in my worsheet:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iSect As Range

Set iSect = Application.Intersect(Range(Target.Address),
Range("BG7:DD60"))

If iSect Is Nothing Or Target.Cells.Count 1 Then
Exit Sub
End If

Application.EnableEvents = False

Select Case Target.Value
Case Is = 0
With Target.Interior
.ColorIndex = xlNone
End With

Case Is < Range("c2")
With Target.Interior
.ColorIndex = 1
End With
With Target.Font
.ColorIndex = 10
End With

Case Is < Range("c3")
With Target.Interior
.ColorIndex = 6
End With
With Target.Font
.ColorIndex = 40
End With

Case Is = "n/a"
With Target.Interior
.ColorIndex = 15
End With

Case Is = "On hold"
With Target.Interior
.ColorIndex = 3
End With

End Select

Application.EnableEvents = True

End Sub

however the cells in the range are popoulated via the following equation:

=IF(OR(INDIRECT(VLOOKUP($B7,REFERENCE!$S$4:$T$56,2 ,FALSE)&"!$C9:$EC9")2),IF(OR(INDIRECT(VLOOKUP($B7 ,REFERENCE!$S$4:$T$56,2,FALSE)&"!$C9:$EC9")="n/a"),"n/a",(MAX(INDIRECT(VLOOKUP($B7,REFERENCE!$S$4:$T$56, 2,FALSE)&"!$C9:$EC9")))),MAX((INDIRECT(VLOOKUP($B7 ,REFERENCE!$S$4:$T$56,2,FALSE)&"!$C9:$EC9"))*(INDI RECT(VLOOKUP($B7,REFERENCE!$S$4:$T$56,2,FALSE)&"!$ C$6:$EC$6"))))

The only way i can seem to get the macros to run is if i go into each cell
and press CNTRL+SHIFT + ENTER ( array formula)

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 55
Default macros doesn't execute unless i go into cell and press enter.

Do you mean instead of using VBA just use the normal conditional formatting
allowed by excel? That limits me to 3 colours and i have at least 5 that i
want to use.
Is there a macro that would do the equivalent of going into any way to go
into each cell pressing cntrl+ shift +enter? The information in the range is
going to be copy and paste elsewhere in my worksheet. So, i could apply the
conditional formatting to the pasted data, but i get the same problem in that
the macro doesn't execute on beacsue it doesn't read it as a cell change.

Thanks for your help.

"Jim Thomlinson" wrote:

The code that you have requires a change be made to the cell. The value of
the cell changing because the cell recalculates does not count as a change.
Normally you could look at changes in the precident cells of the calculation
but since you are using indirect that it kinda out of the question. But
looking at the code my question to you would be why not just use a regular
built in conditional format?
--
HTH...

Jim Thomlinson


"Emma" wrote:

HI,
i am very new to Macros and VBA and i the following conditional formatting
ocde in my worsheet:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iSect As Range

Set iSect = Application.Intersect(Range(Target.Address),
Range("BG7:DD60"))

If iSect Is Nothing Or Target.Cells.Count 1 Then
Exit Sub
End If

Application.EnableEvents = False

Select Case Target.Value
Case Is = 0
With Target.Interior
.ColorIndex = xlNone
End With

Case Is < Range("c2")
With Target.Interior
.ColorIndex = 1
End With
With Target.Font
.ColorIndex = 10
End With

Case Is < Range("c3")
With Target.Interior
.ColorIndex = 6
End With
With Target.Font
.ColorIndex = 40
End With

Case Is = "n/a"
With Target.Interior
.ColorIndex = 15
End With

Case Is = "On hold"
With Target.Interior
.ColorIndex = 3
End With

End Select

Application.EnableEvents = True

End Sub

however the cells in the range are popoulated via the following equation:

=IF(OR(INDIRECT(VLOOKUP($B7,REFERENCE!$S$4:$T$56,2 ,FALSE)&"!$C9:$EC9")2),IF(OR(INDIRECT(VLOOKUP($B7 ,REFERENCE!$S$4:$T$56,2,FALSE)&"!$C9:$EC9")="n/a"),"n/a",(MAX(INDIRECT(VLOOKUP($B7,REFERENCE!$S$4:$T$56, 2,FALSE)&"!$C9:$EC9")))),MAX((INDIRECT(VLOOKUP($B7 ,REFERENCE!$S$4:$T$56,2,FALSE)&"!$C9:$EC9"))*(INDI RECT(VLOOKUP($B7,REFERENCE!$S$4:$T$56,2,FALSE)&"!$ C$6:$EC$6"))))

The only way i can seem to get the macros to run is if i go into each cell
and press CNTRL+SHIFT + ENTER ( array formula)

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Cannot view cell content until I press enter AnisKhan Excel Discussion (Misc queries) 4 June 9th 06 06:06 PM
Hitting Enter to Move Cursor to the Next Entry Cell Cisnerax Excel Worksheet Functions 3 February 25th 06 12:04 PM
Stop next cell being selected on Enter Ken G. Excel Discussion (Misc queries) 2 December 6th 05 08:21 AM
Stop next cell being selected on Enter Jeff Stevens Excel Discussion (Misc queries) 0 December 6th 05 07:55 AM
must press enter 2x to move cell focus Lynn Excel Discussion (Misc queries) 0 March 18th 05 07:05 PM


All times are GMT +1. The time now is 11:17 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"