macro problem

[project manager]

hi,

having problems getting my macro to work.

i want it so that you can only edit rows in e5 to k27 once O/C/A is picked
from the drop down in b5 to b27. ie i want people to fill in B5, then the
rest of the row.

if it helps set the scene each row is a task/project, and column b is a type
of work, e to k is sat - fri where hours are entered...

i also have a problem with when data is pasted in it doesnt trigger the
event change.

any help would be awesome,

cheers

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long
If Not Intersect(Target, Range("E5:K27")) Is Nothing And Target.Count = 1 Then
'it is one of E5:K27

i = Target.Row
Select Case Range("B5" & i).Value
Case "O"
Range("E" & i & ":K" & i).Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue,
Operator:=xlEqual, _
Formula1:="0"
Selection.FormatConditions(1).Font.ColorIndex = 2
Selection.FormatConditions.Add Type:=xlCellValue,
Operator:=xlGreater, _
Formula1:="0"
Selection.FormatConditions(1).Font.ColorIndex = 1
Range("m5").Select

Case "C"
Range("E" & i & ":K" & i).Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue,
Operator:=xlEqual, _
Formula1:="0"
Selection.FormatConditions(1).Font.ColorIndex = 2
Selection.FormatConditions.Add Type:=xlCellValue,
Operator:=xlGreater, _
Formula1:="0"
Selection.FormatConditions(1).Font.ColorIndex = 1
Range("m5").Select
Case "A"
Range("E" & i & ":K" & i).Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue,
Operator:=xlEqual, _
Formula1:="0"
Selection.FormatConditions(1).Font.ColorIndex = 2
Selection.FormatConditions.Add Type:=xlCellValue,
Operator:=xlGreater, _
Formula1:="0"
Selection.FormatConditions(1).Font.ColorIndex = 1
Range("m5").Select

Case Else
Application.EnableEvents = False
Range("E" & i & ":K" & i).Value = 0
Range("E" & i & ":K" & i).Font.ColorIndex = 2
MsgBox "PLEASE COMPLETE O/C/A"
Application.EnableEvents = True
End Select
End If
End Sub

 

[Joel]

First, worksheet change only works on one sheet and must be place in the VBA
Sheet where you are changing data. VBA has 3 types sheets
1) Thisworkbook
2) Modules
3) Sheets - one fror every worksheet in the workbook.

Make sure you are in the correct sheet.


Then place a break point on the SUB line of your macro by clicking with the
mouse on the line and then press F9. Go back to worksheet and make a change.
The macro should stop on the 1st line. Then step through the code using F8.
This should help you isolate the problem.

 

[project manager]

its in sheets and when i put O , C or A in column b it stays white.

 

[Joel]

Did you add the break point like I said? Also does other macros run. check
your security settings from worksheet menu Tools - Options - Macro -Security
and make sure you are at medium level. Also when you open your workbook make
sure you check that you want macros enabled.

 

[project manager]

it toggles through with ut any errors in the code, just how its written/ what
it performs...

 

[Joel]

You originally said the macro wasn't running when a change was made. When
you set the break point you found the code was being activate. Your problem
may be with the application Enable Events. If you get an error while in the
function it will disable future events until you enable the event again. I
rewrote the macro below the way it should be written.

Also you had this error
from
Range("B5" & i).
to
Range("B" & i).


Private Sub Worksheet_Change(ByVal Target As Range)


Application.EnableEvents = False

For Each cell In Target
If Not Intersect(Target, Range("E5:K27")) Is Nothing Then
'it is one of E5:K27

With cell
i = cell.Row
Select Case Range("B" & i).Value
Case "O", "C", "A"
.FormatConditions.Delete
.FormatConditions.Add Type:=xlCellValue, _
Operator:=xlEqual, _
Formula1:="0"
.FormatConditions(1).Font.ColorIndex = 2
.FormatConditions.Add Type:=xlCellValue, _
Operator:=xlGreater, _
Formula1:="0"
.FormatConditions(1).Font.ColorIndex = 1


Case Else
.Value = 0
.Font.ColorIndex = 2
MsgBox "PLEASE COMPLETE O/C/A"
End Select
End With
End If
Next cell
Application.EnableEvents = True
End Sub

 

[project manager]

its nearly there, just the formatting. if o/c/a is in b and the value is =
to 0 make text white, if not black. if no o c a the zero it and white...

 

[Joel]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long

Application.EnableEvents = False

For Each cell In Target
If Not Intersect(Target, Range("E5:K27")) Is Nothing Then
'it is one of E5:K27

With cell

Select Case Range("B5" & i).Value
Case "O", "C", "A"
.FormatConditions.Delete
.FormatConditions.Add Type:=xlCellValue, _
Operator:=xlEqual, _
Formula1:="0"

.FormatConditions.Add Type:=xlCellValue, _
Operator:=xlGreater, _
Formula1:="0"
If cell.Value = 0 Then
.FormatConditions(1).Font.ColorIndex = 2
Else
.FormatConditions(1).Font.ColorIndex = 1
End If

Case Else
.Value = 0
.Font.ColorIndex = 2
MsgBox "PLEASE COMPLETE O/C/A"
End Select
End With
End If
Next cell
Application.EnableEvents = True
End Sub

 

[Joel]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long

Application.EnableEvents = False

For Each cell In Target
If Not Intersect(Target, Range("E5:K27")) Is Nothing Then
'it is one of E5:K27

With cell

Select Case Range("B5" & i).Value
Case "O", "C", "A"
.FormatConditions.Delete
.FormatConditions.Add Type:=xlCellValue, _
Operator:=xlEqual, _
Formula1:="0"

.FormatConditions.Add Type:=xlCellValue, _
Operator:=xlGreater, _
Formula1:="0"
If cell.Value = 0 Then
.FormatConditions(1).Font.ColorIndex = 2
Else
.FormatConditions(1).Font.ColorIndex = 1
End If

Case Else
.Value = 0
.Font.ColorIndex = 2
MsgBox "PLEASE COMPLETE O/C/A"
End Select
End With
End If
Next cell
Application.EnableEvents = True
End Sub