file name using a templete

K

Kelly********

Thank to all that helped with my project.
This is the code I want to use. It seems to work ok and I
can kind of follow it. As you can see I'm using
KY2D2003.Xls as the file name and the code depends on it.
My final question I hope is. If I save it as a template
when you pull it up office rename the file to
KY2D20031.Xls and so on. How can I make the code
corresponded with what ever the file is named with out
changing the code dramatically? I would like to just be
able to use some variable or something to replace
KY2D2003.Xls in the code.


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Rem if First name <> "" and Last name = ""
Rem if Last name <> "" and First name = ""
Rem if Firstname and Lastname <> "" and (NULL22 or NULL23
<> "X" Error
Rem if NULL22 or NULL23 = "X" and First name or Lastname
="" error
Rem if NULL22 <> "" and NULL22 <> "X" error
Rem if NULL23 <> "" and NULL23 <> "X" error

GoTo Firstpass

Start:
GoTo Quit

Firstpass:
Rem INFO IN CELL F16 MAKES CELL F14 RED.
If Range("[KY2D2003.Xls]Form_740!First") <> "" And Range
("[KY2D2003.Xls]Form_740!Last") = "" Then
Range("[KY2D2003.Xls]Form_740!
Last").Interior.ColorIndex = 3
Range("[KY2D2003.Xls]Form_740!
First").Interior.ColorIndex = X1none
Range("[KY2D2003.Xls]Form_740!
NULL22").Interior.ColorIndex = X1none
Range("[KY2D2003.Xls]Form_740!
NULL23").Interior.ColorIndex = X1none
GoTo Start
End If

Rem CELL F14 MAKES CELL F16 RED.
If Range("[KY2D2003.Xls]Form_740!First") = "" And Range
("[KY2D2003.Xls]Form_740!Last") <> "" Then
Range("[KY2D2003.Xls]Form_740!
Last").Interior.ColorIndex = X1none
Range("[KY2D2003.Xls]Form_740!
First").Interior.ColorIndex = 3
Range("[KY2D2003.Xls]Form_740!
NULL22").Interior.ColorIndex = X1none
Range("[KY2D2003.Xls]Form_740!
NULL23").Interior.ColorIndex = X1none
GoTo Start
End If

Rem INFO IN CELLS F14 AND F16 MAKES CELLS F22 AND F23 RED.
If Range("[KY2D2003.Xls]Form_740!First") <> "" And Range
("[KY2D2003.Xls]Form_740!Last") <> "" And _
(Range("[KY2D2003.Xls]Form_740!NULL22") <> "X" And
Range("[KY2D2003.Xls]Form_740!NULL23") <> "X") Then
Range("[KY2D2003.Xls]Form_740!
NULL22").Interior.ColorIndex = 3
Range("[KY2D2003.Xls]Form_740!
NULL23").Interior.ColorIndex = 3
Range("[KY2D2003.Xls]Form_740!
First").Interior.ColorIndex = X1none
Range("[KY2D2003.Xls]Form_740!
Last").Interior.ColorIndex = X1none
GoTo Start
End If

Rem AN X IN CELLS F22 OR F23 MAKES CELL F16 RED.
If (Range("[KY2D2003.Xls]Form_740!NULL22") = "X" Or Range
("[KY2D2003.Xls]Form_740!NULL23") = "X") And _
Range("[KY2D2003.Xls]Form_740!First") = "" Then
Range("[KY2D2003.Xls]Form_740!
First").Interior.ColorIndex = 3
Range("[KY2D2003.Xls]Form_740!
NULL22").Interior.ColorIndex = X1none
Range("[KY2D2003.Xls]Form_740!
NULL23").Interior.ColorIndex = X1none
Range("[KY2D2003.Xls]Form_740!
Last").Interior.ColorIndex = X1none
GoTo Start
End If

Rem AND X IN CELLS F22 OR F23 MAKES CELL F14 RED.
If (Range("[KY2D2003.Xls]Form_740!NULL22") = "X" Or Range
("[KY2D2003.Xls]Form_740!NULL23") = "X") And _
Range("[KY2D2003.Xls]Form_740!Last") = "" Then
Range("[KY2D2003.Xls]Form_740!
Last").Interior.ColorIndex = 3
Range("[KY2D2003.Xls]Form_740!
NULL22").Interior.ColorIndex = X1none
Range("[KY2D2003.Xls]Form_740!
NULL23").Interior.ColorIndex = X1none
Range("[KY2D2003.Xls]Form_740!
First").Interior.ColorIndex = X1none
GoTo Start
End If

Rem ANYTHING BUT A X IN CELL F22 MAKES F22 RED.
If (Range("[KY2D2003.Xls]Form_740!NULL22") <> "X" And Range
("[KY2D2003.Xls]Form_740!NULL22") <> "") Then
Range("[KY2D2003.Xls]Form_740!
NULL22").Interior.ColorIndex = 3
Range("[KY2D2003.Xls]Form_740!
First").Interior.ColorIndex = X1none
Range("[KY2D2003.Xls]Form_740!
NULL23").Interior.ColorIndex = X1none
Range("[KY2D2003.Xls]Form_740!
Last").Interior.ColorIndex = X1none
GoTo Start
End If

Rem ANYTHING BUT AND X IN CELL F23 MAKES CELL F23 RED.
If (Range("[KY2D2003.Xls]Form_740!NULL23") <> "X" And Range
("[KY2D2003.Xls]Form_740!NULL23") <> "") Then
Range("[KY2D2003.Xls]Form_740!
NULL23").Interior.ColorIndex = 3
Range("[KY2D2003.Xls]Form_740!
First").Interior.ColorIndex = X1none
Range("[KY2D2003.Xls]Form_740!
NULL22").Interior.ColorIndex = X1none
Range("[KY2D2003.Xls]Form_740!
Last").Interior.ColorIndex = X1none
GoTo Start
End If

Rem MAKES CELL F14, F16, F22, F23 DEFAULT COLOR.
Range("[KY2D2003.Xls]Form_740!
First").Interior.ColorIndex = X1none
Range("[KY2D2003.Xls]Form_740!
NULL22").Interior.ColorIndex = X1none
Range("[KY2D2003.Xls]Form_740!
Last").Interior.ColorIndex = X1none
Range("[KY2D2003.Xls]Form_740!
NULL23").Interior.ColorIndex = X1none


Quit:

End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Making a cell only ecept an X and protecting code 2
Changing worksheet backgrounds from one control sheet 0
Pass condition formatting across worksheets via vba. 0
Cell patterns and Cell entries 7
Auto fit text in linked cells of a protected worksheet 1
Cells change based on other cell input 2
Change row colors based on a cell value 1
Conditional formatting Run-time error '5' 0

Top