conditional formatting w/VBA - works for some, not others

[Savalou Dave]

Hello,

I have a spreadsheet survey that changes background color depending on
the response provided by a data validation dropdown list. There are
more than three responses so I used a VBA module I found on this site
to code for five conditions. The code I'm using is below. The
problem I'm having is that this works on most computers, but on some
the colors will not change. I re-compiled on a machine where it did
not work and that did not solve the problem. The reference libraries
are also the same. Anyone have any ideas?

Thanks very much,

Dave

Private Sub Worksheet_Change(ByVal Target As Range)
'David McRitchie, 2000-08-08 rev. 2000-08-14
' http://www.mvps.org/dmcritchie/excel/event.htm
Dim vLetter As String
Dim vColor As Long
Dim cRange As Range
Dim cell As Range
'***************** check range ****
Set cRange = Intersect(Range("c8:c72"), Range(Target(1).Address))
If cRange Is Nothing Then Exit Sub
'**********************************
For Each cell In Target
vLetter = UCase(Left(cell.Value & " ", 3))
'see colors.htm and event.htm in same directory as
' http://www.mvps.org/dmcritchie/excel/excel.htm
vColor = 0 'default is no color
Select Case vLetter
Case "YES"
vColor = 4
Case "NO "
vColor = 3
Case "MOS"
vColor = 6
Case "PAR"
vColor = 45
Case "N/A"
vColor = 2


End Select
Application.EnableEvents = False 'should be part of Change
macro
cell.Interior.ColorIndex = vColor
Application.EnableEvents = True 'should be part of Change macro
Next cell
'Target.Offset(0, 1).Interior.colorindex = vColor
' use Text instead of Interior if you prefer
End Sub

 

[Jim Cone]

Dave,
EnableEvents may have been left inoperative on those machines.
Run this line of code on a non-operative machine and see if the problem is fixed...
Application.EnableEvents = True

It appears to me as if you do not need the two lines of code that
disable and enable events. Changing a cell color does trigger an event.

If you do not remove the two lines then you need to add error handling to the
code and enable events in the error handler.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"Savalou Dave" <[email protected]>
wrote in message
Hello,
I have a spreadsheet survey that changes background color depending on
the response provided by a data validation dropdown list. There are
more than three responses so I used a VBA module I found on this site
to code for five conditions. The code I'm using is below. The
problem I'm having is that this works on most computers, but on some
the colors will not change. I re-compiled on a machine where it did
not work and that did not solve the problem. The reference libraries
are also the same. Anyone have any ideas?
Thanks very much,
Dave

Private Sub Worksheet_Change(ByVal Target As Range)
'David McRitchie, 2000-08-08 rev. 2000-08-14
' http://www.mvps.org/dmcritchie/excel/event.htm
Dim vLetter As String
Dim vColor As Long
Dim cRange As Range
Dim cell As Range
'***************** check range ****
Set cRange = Intersect(Range("c8:c72"), Range(Target(1).Address))
If cRange Is Nothing Then Exit Sub
'**********************************
For Each cell In Target
vLetter = UCase(Left(cell.Value & " ", 3))
'see colors.htm and event.htm in same directory as
' http://www.mvps.org/dmcritchie/excel/excel.htm
vColor = 0 'default is no color
Select Case vLetter
Case "YES"
vColor = 4
Case "NO "
vColor = 3
Case "MOS"
vColor = 6
Case "PAR"
vColor = 45
Case "N/A"
vColor = 2
End Select
Application.EnableEvents = False 'should be part of Change macro
cell.Interior.ColorIndex = vColor
Application.EnableEvents = True 'should be part of Change macro
Next cell
'Target.Offset(0, 1).Interior.colorindex = vColor
' use Text instead of Interior if you prefer
End Sub

 

[Jim Cone]

Correction...

"Changing a cell color does trigger an event."
should read...
"Changing a cell color not does trigger an event."