VBA Help needed

[Darin Hylbert]

I am trying to set up a macro that will identify if a field has bee
changed. I need the syntax for how to get this done. Below is th
original Macro. I want to see if someone changes a field from th
default below. Can someone help?
Thanks in advance,


Sub CEO_OC()
Dim LR As Long
Dim i As Long
LR = Range("D" & Rows.Count).End(xlUp).Row
For i = 2 To LR
If Range("D" & i).Value = "Facility CEO" Then
Range("f" & i).Value = ""
Range("j" & i).Value = "X"
Range("n" & i).Value = ""
Range("r" & i).Value = ""
Range("v" & i).Value = "X"
Range("z" & i).Value = ""
Range("ad" & i).Value = "X"
Range("ah" & i).Value = ""
Range("al" & i).Value = "X"
Range("ap" & i).Value = ""
Range("at" & i).Value = ""
Range("ax" & i).Value = "X"
Range("bb" & i).Value = ""
Range("bf" & i).Value = ""
Range("bj" & i).Value = ""
Range("bn" & i).Value = "X"
Range("br" & i).Value = ""
Range("bu" & i).Value = ""
Range("bz" & i).Value = ""
Range("cd" & i).Value = ""
Range("ch" & i).Value = ""
Range("cl" & i).Value = ""
Range("cp" & i).Value = "X"

End If
Next i
End Su

 

[Ben McClave]

Darin,

This is ugly, but it works:

Sub Check_CEO_OC()
Dim LR As Long
Dim i As Long
LR = Range("D" & Rows.Count).End(xlUp).Row
For i = 2 To LR
If Range("D" & i).Value = "Facility CEO" Then
If Range("f" & i).Value <> "" Then MsgBox "Cell F" & i & " Changed"
If Range("j" & i).Value <> "X" Then MsgBox "Cell J" & i & " Changed"
If Range("n" & i).Value <> "" Then MsgBox "Cell N" & i & " Changed"
If Range("r" & i).Value <> "" Then MsgBox "Cell R" & i & " Changed"
If Range("v" & i).Value <> "X" Then MsgBox "Cell V" & i & " Changed"
If Range("z" & i).Value <> "" Then MsgBox "Cell Z" & i & " Changed"
If Range("ad" & i).Value <> "X" Then MsgBox "Cell AD" & i & " Changed"
If Range("ah" & i).Value <> "" Then MsgBox "Cell AH" & i & " Changed"
If Range("al" & i).Value <> "X" Then MsgBox "Cell AL" & i & " Changed"
If Range("ap" & i).Value <> "" Then MsgBox "Cell AP" & i & " Changed"
If Range("at" & i).Value <> "" Then MsgBox "Cell AT" & i & " Changed"
If Range("ax" & i).Value <> "X" Then MsgBox "Cell AX" & i & " Changed"
If Range("bb" & i).Value <> "" Then MsgBox "Cell BB" & i & " Changed"
If Range("bf" & i).Value <> "" Then MsgBox "Cell BF" & i & " Changed"
If Range("bj" & i).Value <> "" Then MsgBox "Cell BJ" & i & " Changed"
If Range("bn" & i).Value <> "X" Then MsgBox "Cell BN" & i & " Changed"
If Range("br" & i).Value <> "" Then MsgBox "Cell BR" & i & " Changed"
If Range("bu" & i).Value <> "" Then MsgBox "Cell BU" & i & " Changed"
If Range("bz" & i).Value <> "" Then MsgBox "Cell BZ" & i & " Changed"
If Range("cd" & i).Value <> "" Then MsgBox "Cell CD" & i & " Changed"
If Range("ch" & i).Value <> "" Then MsgBox "Cell CH" & i & " Changed"
If Range("cl" & i).Value <> "" Then MsgBox "Cell CL" & i & " Changed"
If Range("cp" & i).Value <> "X" Then MsgBox "Cell CP" & i & " Changed"

End If
Next i
End Sub

 

[GS]

Perhaps...

Sub Check_CEO_OC2()
Dim i&, v As Variant
Const sX_values$ = "J,V,AD,AL,AX,BN,CP"
Const sEmptyValues$ = "F,N,R,Z,AH,AP,AT,BB,BF,BJ,BR,BU,BZ,CD,CH,CL"

For i = 2 To Range("D" & Rows.Count).End(xlUp).Row
If Range("D" & i).Value = "Facility CEO" Then
For Each v In Split(sX_values, ",")
If Range(v & i) <> "X" Then _
MsgBox "Cell " & v & " " & i & " Changed"
Next 'v
For Each v In Split(sEmptyValues, ",")
If Range(v & i) <> "" Then _
MsgBox "Cell " & v & " " & i & " Changed"
Next 'v
End If
Next i
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion