I hope my subject line is appropriate to the request....
Running Excel 2003, I have a spreadsheet which tracks new contract
opportunities for our company. There are many stages involved in the
contract process, and many people involved. Each contract is listed on a
single line, and column descriptions follow:
A=Blank
B-L=General information, such as Dealer Information, Acct. Exec Name & #,
Acct Rep, Type of Location, Date Requested, and so on.
M=Blank
N-AH=Contract Stages, where user enters dates of stage completion.

There are (9) different request types, which are entered into Column J. I
need to color in/highlight different cells within the N-AH range, dependent
on the Contract Type entered in Column J. This is to indicate need for
information in those columns. For example, for contract type C, I need to
highlight O:P, & W:X on the respective row. With my cursor in column B, I
recorded the following macro for"contract type C":

Sub Contract_C()
'
' Contract_C Macro
' Macro recorded 9/12/2007 by C11163 To highlight Contract Tracker cells for
Type C.
'
' Keyboard Shortcut: Ctrl+Shift+D
'
ActiveCell.Offset(0, 8).Range("A1").Select
ActiveCell.FormulaR1C1 = "C"
With ActiveCell.Characters(Start:=1, Length:=1).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
ActiveCell.Offset(0, 5).Range("A1,A1:B1").Select
ActiveWindow.SmallScroll ToRight:=4
ActiveCell.Range("A1,A1:B1,I1:J1").Select
ActiveCell.Offset(0, 8).Range("A1").Activate
With Selection.Interior
.ColorIndex = 35
.Pattern = xlSolid
End With
ActiveCell.Offset(0, -21).Range("A1").Select
End Sub

The problem is, I want to be able to enter the "C" (or other designation),
into column J, regardless of where on the particular row I am at, and color
in cells O:P, W:X on same row, and then return to column K.

I apologize for the lengthy post, however I am still new at macros & all
help is appreciated! If this would be easier covered off-line, I am amenable
to that also.

TIA!
Sandi

I think this is what you want but let me know...

Sub Contract_C()
Dim rng As Range

Set rng = Cells(ActiveCell.Row, "J")
With rng
.Value = "C"
With .Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
End With
.Offset(0, 5).Resize(, 2).Interior.ColorIndex = 35
.Offset(0, 13).Resize(, 2).Interior.ColorIndex = 35
End With
End Sub
--
HTH...

Jim Thomlinson


"RUSH2CROCHET" wrote:

I hope my subject line is appropriate to the request....
Running Excel 2003, I have a spreadsheet which tracks new contract
opportunities for our company. There are many stages involved in the
contract process, and many people involved. Each contract is listed on a
single line, and column descriptions follow:
A=Blank
B-L=General information, such as Dealer Information, Acct. Exec Name & #,
Acct Rep, Type of Location, Date Requested, and so on.
M=Blank
N-AH=Contract Stages, where user enters dates of stage completion.

There are (9) different request types, which are entered into Column J. I
need to color in/highlight different cells within the N-AH range, dependent
on the Contract Type entered in Column J. This is to indicate need for
information in those columns. For example, for contract type C, I need to
highlight O:P, & W:X on the respective row. With my cursor in column B, I
recorded the following macro for"contract type C":

Sub Contract_C()
'
' Contract_C Macro
' Macro recorded 9/12/2007 by C11163 To highlight Contract Tracker cells for
Type C.
'
' Keyboard Shortcut: Ctrl+Shift+D
'
ActiveCell.Offset(0, 8).Range("A1").Select
ActiveCell.FormulaR1C1 = "C"
With ActiveCell.Characters(Start:=1, Length:=1).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
ActiveCell.Offset(0, 5).Range("A1,A1:B1").Select
ActiveWindow.SmallScroll ToRight:=4
ActiveCell.Range("A1,A1:B1,I1:J1").Select
ActiveCell.Offset(0, 8).Range("A1").Activate
With Selection.Interior
.ColorIndex = 35
.Pattern = xlSolid
End With
ActiveCell.Offset(0, -21).Range("A1").Select
End Sub

The problem is, I want to be able to enter the "C" (or other designation),
into column J, regardless of where on the particular row I am at, and color
in cells O:P, W:X on same row, and then return to column K.

I apologize for the lengthy post, however I am still new at macros & all
help is appreciated! If this would be easier covered off-line, I am amenable
to that also.

TIA!
Sandi

THANK YOU, THANK YOU! You are exactly correct! That is precisely what I
need. As I don't think you want to write all my code, and I want to further
my code knowledge, I have another question.

I understand the .offset(0, 5) to be "go 5 columns to the right... but, what
exactly is Resize(, 2).
I'm thinking perhaps the (, 2) indicates to select the current plus next cell.

I will need to know this as one of my other contract types requires me to
color N-AI. In this case I would change my .offset(0, 4) to start in column
M, and then .Resize(, 22) to end in column AI.

Please confirm if my thoughts are correct.

Thanks again!
Sandi

"RUSH2CROCHET" wrote:

I hope my subject line is appropriate to the request....
Running Excel 2003, I have a spreadsheet which tracks new contract
opportunities for our company. There are many stages involved in the
contract process, and many people involved. Each contract is listed on a
single line, and column descriptions follow:
A=Blank
B-L=General information, such as Dealer Information, Acct. Exec Name & #,
Acct Rep, Type of Location, Date Requested, and so on.
M=Blank
N-AH=Contract Stages, where user enters dates of stage completion.

There are (9) different request types, which are entered into Column J. I
need to color in/highlight different cells within the N-AH range, dependent
on the Contract Type entered in Column J. This is to indicate need for
information in those columns. For example, for contract type C, I need to
highlight O:P, & W:X on the respective row. With my cursor in column B, I
recorded the following macro for"contract type C":

Sub Contract_C()
'
' Contract_C Macro
' Macro recorded 9/12/2007 by C11163 To highlight Contract Tracker cells for
Type C.
'
' Keyboard Shortcut: Ctrl+Shift+D
'
ActiveCell.Offset(0, 8).Range("A1").Select
ActiveCell.FormulaR1C1 = "C"
With ActiveCell.Characters(Start:=1, Length:=1).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
ActiveCell.Offset(0, 5).Range("A1,A1:B1").Select
ActiveWindow.SmallScroll ToRight:=4
ActiveCell.Range("A1,A1:B1,I1:J1").Select
ActiveCell.Offset(0, 8).Range("A1").Activate
With Selection.Interior
.ColorIndex = 35
.Pattern = xlSolid
End With
ActiveCell.Offset(0, -21).Range("A1").Select
End Sub

The problem is, I want to be able to enter the "C" (or other designation),
into column J, regardless of where on the particular row I am at, and color
in cells O:P, W:X on same row, and then return to column K.

I apologize for the lengthy post, however I am still new at macros & all
help is appreciated! If this would be easier covered off-line, I am amenable
to that also.

TIA!
Sandi

You are correct. The first thing my code does is it sets a range object
(similar to activecell) to whatever row you are on and column J. That is my
reference point.
I add the C to this spot and format the font. Next I define a range that is
5 columns to the right and resized 0 rows and 2 columns larger. It changes
the interior colour of that defined range to 35. Same deal with the next line
of code...

Things to note are that I do not move the active cell and I do not select
any cells. I use my range object instead. This makes the code more compact
and efficient while avoiding having the cursor flying around the screen.
--
HTH...

Jim Thomlinson


"RUSH2CROCHET" wrote:

THANK YOU, THANK YOU! You are exactly correct! That is precisely what I
need. As I don't think you want to write all my code, and I want to further
my code knowledge, I have another question.

I understand the .offset(0, 5) to be "go 5 columns to the right... but, what
exactly is Resize(, 2).
I'm thinking perhaps the (, 2) indicates to select the current plus next cell.

I will need to know this as one of my other contract types requires me to
color N-AI. In this case I would change my .offset(0, 4) to start in column
M, and then .Resize(, 22) to end in column AI.

Please confirm if my thoughts are correct.

Thanks again!
Sandi

"RUSH2CROCHET" wrote:

I hope my subject line is appropriate to the request....
Running Excel 2003, I have a spreadsheet which tracks new contract
opportunities for our company. There are many stages involved in the
contract process, and many people involved. Each contract is listed on a
single line, and column descriptions follow:
A=Blank
B-L=General information, such as Dealer Information, Acct. Exec Name & #,
Acct Rep, Type of Location, Date Requested, and so on.
M=Blank
N-AH=Contract Stages, where user enters dates of stage completion.

There are (9) different request types, which are entered into Column J. I
need to color in/highlight different cells within the N-AH range, dependent
on the Contract Type entered in Column J. This is to indicate need for
information in those columns. For example, for contract type C, I need to
highlight O:P, & W:X on the respective row. With my cursor in column B, I
recorded the following macro for"contract type C":

Sub Contract_C()
'
' Contract_C Macro
' Macro recorded 9/12/2007 by C11163 To highlight Contract Tracker cells for
Type C.
'
' Keyboard Shortcut: Ctrl+Shift+D
'
ActiveCell.Offset(0, 8).Range("A1").Select
ActiveCell.FormulaR1C1 = "C"
With ActiveCell.Characters(Start:=1, Length:=1).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
ActiveCell.Offset(0, 5).Range("A1,A1:B1").Select
ActiveWindow.SmallScroll ToRight:=4
ActiveCell.Range("A1,A1:B1,I1:J1").Select
ActiveCell.Offset(0, 8).Range("A1").Activate
With Selection.Interior
.ColorIndex = 35
.Pattern = xlSolid
End With
ActiveCell.Offset(0, -21).Range("A1").Select
End Sub

The problem is, I want to be able to enter the "C" (or other designation),
into column J, regardless of where on the particular row I am at, and color
in cells O:P, W:X on same row, and then return to column K.

I apologize for the lengthy post, however I am still new at macros & all
help is appreciated! If this would be easier covered off-line, I am amenable
to that also.

TIA!
Sandi

Jim:

Thanks - I feel smarter already! :-)

If you don't mind, perhaps just a little more guidance?! The "Sub
Contract_C" is the "macro name" I assigned when using the macro recorder. In
VB, I note that it appears in the "Declaration" field, so that you know what
sub procedure you are editing. If you record subsequent macros, there is a
line separating the macro code, however, If writing additional declarations
using Visual Basic, is it a "Break" that you enter to get the line separating
the different sub procedures, or does VB recognize a new sub procedure by the
formatting of the beginning statement? For example...
Sub Contract_C()
Dim rng As Range

Sub Contract_N()
Dim Rng As Range

Also, how would you delete an "empty" module?

Thanks once again for all your help!
Sandi

"Jim Thomlinson" wrote:

You are correct. The first thing my code does is it sets a range object
(similar to activecell) to whatever row you are on and column J. That is my
reference point.
I add the C to this spot and format the font. Next I define a range that is
5 columns to the right and resized 0 rows and 2 columns larger. It changes
the interior colour of that defined range to 35. Same deal with the next line
of code...

Things to note are that I do not move the active cell and I do not select
any cells. I use my range object instead. This makes the code more compact
and efficient while avoiding having the cursor flying around the screen.
--
HTH...

Jim Thomlinson


"RUSH2CROCHET" wrote:

THANK YOU, THANK YOU! You are exactly correct! That is precisely what I
need. As I don't think you want to write all my code, and I want to further
my code knowledge, I have another question.

I understand the .offset(0, 5) to be "go 5 columns to the right... but, what
exactly is Resize(, 2).
I'm thinking perhaps the (, 2) indicates to select the current plus next cell.

I will need to know this as one of my other contract types requires me to
color N-AI. In this case I would change my .offset(0, 4) to start in column
M, and then .Resize(, 22) to end in column AI.

Please confirm if my thoughts are correct.

Thanks again!
Sandi

"RUSH2CROCHET" wrote:

I hope my subject line is appropriate to the request....
Running Excel 2003, I have a spreadsheet which tracks new contract
opportunities for our company. There are many stages involved in the
contract process, and many people involved. Each contract is listed on a
single line, and column descriptions follow:
A=Blank
B-L=General information, such as Dealer Information, Acct. Exec Name & #,
Acct Rep, Type of Location, Date Requested, and so on.
M=Blank
N-AH=Contract Stages, where user enters dates of stage completion.

There are (9) different request types, which are entered into Column J. I
need to color in/highlight different cells within the N-AH range, dependent
on the Contract Type entered in Column J. This is to indicate need for
information in those columns. For example, for contract type C, I need to
highlight O:P, & W:X on the respective row. With my cursor in column B, I
recorded the following macro for"contract type C":

Sub Contract_C()
'
' Contract_C Macro
' Macro recorded 9/12/2007 by C11163 To highlight Contract Tracker cells for
Type C.
'
' Keyboard Shortcut: Ctrl+Shift+D
'
ActiveCell.Offset(0, 8).Range("A1").Select
ActiveCell.FormulaR1C1 = "C"
With ActiveCell.Characters(Start:=1, Length:=1).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
ActiveCell.Offset(0, 5).Range("A1,A1:B1").Select
ActiveWindow.SmallScroll ToRight:=4
ActiveCell.Range("A1,A1:B1,I1:J1").Select
ActiveCell.Offset(0, 8).Range("A1").Activate
With Selection.Interior
.ColorIndex = 35
.Pattern = xlSolid
End With
ActiveCell.Offset(0, -21).Range("A1").Select
End Sub

The problem is, I want to be able to enter the "C" (or other designation),
into column J, regardless of where on the particular row I am at, and color
in cells O:P, W:X on same row, and then return to column K.

I apologize for the lengthy post, however I am still new at macros & all
help is appreciated! If this would be easier covered off-line, I am amenable
to that also.

TIA!
Sandi

VB will automatically place a line between sub procedures so you don't need
to worry about it... To see what I mean just type in

Sub Test

into an existing module and VB will automatically add "End Sub" and place a
line between it and other existing subs.

To remove a module right click on the Module and select "Remove Module?".
You will be prompted "Do you wnat to export... " . Just select No.
--
HTH...

Jim Thomlinson


"RUSH2CROCHET" wrote:

Jim:

Thanks - I feel smarter already! :-)

If you don't mind, perhaps just a little more guidance?! The "Sub
Contract_C" is the "macro name" I assigned when using the macro recorder. In
VB, I note that it appears in the "Declaration" field, so that you know what
sub procedure you are editing. If you record subsequent macros, there is a
line separating the macro code, however, If writing additional declarations
using Visual Basic, is it a "Break" that you enter to get the line separating
the different sub procedures, or does VB recognize a new sub procedure by the
formatting of the beginning statement? For example...
Sub Contract_C()
Dim rng As Range

Sub Contract_N()
Dim Rng As Range

Also, how would you delete an "empty" module?

Thanks once again for all your help!
Sandi

"Jim Thomlinson" wrote:

You are correct. The first thing my code does is it sets a range object
(similar to activecell) to whatever row you are on and column J. That is my
reference point.
I add the C to this spot and format the font. Next I define a range that is
5 columns to the right and resized 0 rows and 2 columns larger. It changes
the interior colour of that defined range to 35. Same deal with the next line
of code...

Things to note are that I do not move the active cell and I do not select
any cells. I use my range object instead. This makes the code more compact
and efficient while avoiding having the cursor flying around the screen.
--
HTH...

Jim Thomlinson


"RUSH2CROCHET" wrote:

THANK YOU, THANK YOU! You are exactly correct! That is precisely what I
need. As I don't think you want to write all my code, and I want to further
my code knowledge, I have another question.

I understand the .offset(0, 5) to be "go 5 columns to the right... but, what
exactly is Resize(, 2).
I'm thinking perhaps the (, 2) indicates to select the current plus next cell.

I will need to know this as one of my other contract types requires me to
color N-AI. In this case I would change my .offset(0, 4) to start in column
M, and then .Resize(, 22) to end in column AI.

Please confirm if my thoughts are correct.

Thanks again!
Sandi

"RUSH2CROCHET" wrote:

I hope my subject line is appropriate to the request....
Running Excel 2003, I have a spreadsheet which tracks new contract
opportunities for our company. There are many stages involved in the
contract process, and many people involved. Each contract is listed on a
single line, and column descriptions follow:
A=Blank
B-L=General information, such as Dealer Information, Acct. Exec Name & #,
Acct Rep, Type of Location, Date Requested, and so on.
M=Blank
N-AH=Contract Stages, where user enters dates of stage completion.

There are (9) different request types, which are entered into Column J. I
need to color in/highlight different cells within the N-AH range, dependent
on the Contract Type entered in Column J. This is to indicate need for
information in those columns. For example, for contract type C, I need to
highlight O:P, & W:X on the respective row. With my cursor in column B, I
recorded the following macro for"contract type C":

Sub Contract_C()
'
' Contract_C Macro
' Macro recorded 9/12/2007 by C11163 To highlight Contract Tracker cells for
Type C.
'
' Keyboard Shortcut: Ctrl+Shift+D
'
ActiveCell.Offset(0, 8).Range("A1").Select
ActiveCell.FormulaR1C1 = "C"
With ActiveCell.Characters(Start:=1, Length:=1).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
ActiveCell.Offset(0, 5).Range("A1,A1:B1").Select
ActiveWindow.SmallScroll ToRight:=4
ActiveCell.Range("A1,A1:B1,I1:J1").Select
ActiveCell.Offset(0, 8).Range("A1").Activate
With Selection.Interior
.ColorIndex = 35
.Pattern = xlSolid
End With
ActiveCell.Offset(0, -21).Range("A1").Select
End Sub

The problem is, I want to be able to enter the "C" (or other designation),
into column J, regardless of where on the particular row I am at, and color
in cells O:P, W:X on same row, and then return to column K.

I apologize for the lengthy post, however I am still new at macros & all
help is appreciated! If this would be easier covered off-line, I am amenable
to that also.

TIA!
Sandi

Thanks for confirming that! Actually between my last post, and your
response, I was able to tested, and found that VB placed the line in for me.

I have all my code written, for the differing scenarios, and they work like
a champ, thanks to all your help & guidance! I am in the process currently
of trying to build the code for deleting the data in B-L, and N-AI, and
de-highlighting the cells in N-AI. So far, I have the following:

Sub Delete_Data()
Dim rng As Range

Set rng = Cells(ActiveCell.Row, "A")
.Offset(0, 1).Resize(, 11).Selection.ClearContents
Selection.Interior.ColorIndex = x1None
.Offset(0, 13).Resize(, 21).Selection.ClearContents
Selection.Interior.ColorIndex = x1None
End Sub

Please look this over, if you don't mind, and tell me where I'm off. Again,
I would like to start & end in Column A.

Thanks so much for all your help - You're a godsend!
Sandi
"Jim Thomlinson" wrote:

VB will automatically place a line between sub procedures so you don't need
to worry about it... To see what I mean just type in

Sub Test

into an existing module and VB will automatically add "End Sub" and place a
line between it and other existing subs.

To remove a module right click on the Module and select "Remove Module?".
You will be prompted "Do you wnat to export... " . Just select No.
--
HTH...

Jim Thomlinson


"RUSH2CROCHET" wrote:

Jim:

Thanks - I feel smarter already! :-)

If you don't mind, perhaps just a little more guidance?! The "Sub
Contract_C" is the "macro name" I assigned when using the macro recorder. In
VB, I note that it appears in the "Declaration" field, so that you know what
sub procedure you are editing. If you record subsequent macros, there is a
line separating the macro code, however, If writing additional declarations
using Visual Basic, is it a "Break" that you enter to get the line separating
the different sub procedures, or does VB recognize a new sub procedure by the
formatting of the beginning statement? For example...
Sub Contract_C()
Dim rng As Range

Sub Contract_N()
Dim Rng As Range

Also, how would you delete an "empty" module?

Thanks once again for all your help!
Sandi

"Jim Thomlinson" wrote:

You are correct. The first thing my code does is it sets a range object
(similar to activecell) to whatever row you are on and column J. That is my
reference point.
I add the C to this spot and format the font. Next I define a range that is
5 columns to the right and resized 0 rows and 2 columns larger. It changes
the interior colour of that defined range to 35. Same deal with the next line
of code...

Things to note are that I do not move the active cell and I do not select
any cells. I use my range object instead. This makes the code more compact
and efficient while avoiding having the cursor flying around the screen.
--
HTH...

Jim Thomlinson


"RUSH2CROCHET" wrote:

THANK YOU, THANK YOU! You are exactly correct! That is precisely what I
need. As I don't think you want to write all my code, and I want to further
my code knowledge, I have another question.

I understand the .offset(0, 5) to be "go 5 columns to the right... but, what
exactly is Resize(, 2).
I'm thinking perhaps the (, 2) indicates to select the current plus next cell.

I will need to know this as one of my other contract types requires me to
color N-AI. In this case I would change my .offset(0, 4) to start in column
M, and then .Resize(, 22) to end in column AI.

Please confirm if my thoughts are correct.

Thanks again!
Sandi

"RUSH2CROCHET" wrote:

I hope my subject line is appropriate to the request....
Running Excel 2003, I have a spreadsheet which tracks new contract
opportunities for our company. There are many stages involved in the
contract process, and many people involved. Each contract is listed on a
single line, and column descriptions follow:
A=Blank
B-L=General information, such as Dealer Information, Acct. Exec Name & #,
Acct Rep, Type of Location, Date Requested, and so on.
M=Blank
N-AH=Contract Stages, where user enters dates of stage completion.

There are (9) different request types, which are entered into Column J. I
need to color in/highlight different cells within the N-AH range, dependent
on the Contract Type entered in Column J. This is to indicate need for
information in those columns. For example, for contract type C, I need to
highlight O:P, & W:X on the respective row. With my cursor in column B, I
recorded the following macro for"contract type C":

Sub Contract_C()
'
' Contract_C Macro
' Macro recorded 9/12/2007 by C11163 To highlight Contract Tracker cells for
Type C.
'
' Keyboard Shortcut: Ctrl+Shift+D
'
ActiveCell.Offset(0, 8).Range("A1").Select
ActiveCell.FormulaR1C1 = "C"
With ActiveCell.Characters(Start:=1, Length:=1).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
ActiveCell.Offset(0, 5).Range("A1,A1:B1").Select
ActiveWindow.SmallScroll ToRight:=4
ActiveCell.Range("A1,A1:B1,I1:J1").Select
ActiveCell.Offset(0, 8).Range("A1").Activate
With Selection.Interior
.ColorIndex = 35
.Pattern = xlSolid
End With
ActiveCell.Offset(0, -21).Range("A1").Select
End Sub

The problem is, I want to be able to enter the "C" (or other designation),
into column J, regardless of where on the particular row I am at, and color
in cells O:P, W:X on same row, and then return to column K.

I apologize for the lengthy post, however I am still new at macros & all
help is appreciated! If this would be easier covered off-line, I am amenable
to that also.

TIA!
Sandi

Selection will be reference the selected cell. You are wanting to work with
you range object and not the selection. Give this a whirl...

Sub Delete_Data()
Dim rng As Range

Set rng = Cells(ActiveCell.Row, "A")
with rng
.Offset(0, 1).Resize(, 11).ClearContents
.Offset(0, 1).Resize(, 11).Interior.ColorIndex = xlNone
.Offset(0, 13).Resize(, 21).ClearContents
.Offset(0, 13).Resize(, 21).Interior.ColorIndex = xlNone
end with
End Sub
--
HTH...

Jim Thomlinson


"RUSH2CROCHET" wrote:

Thanks for confirming that! Actually between my last post, and your
response, I was able to tested, and found that VB placed the line in for me.

I have all my code written, for the differing scenarios, and they work like
a champ, thanks to all your help & guidance! I am in the process currently
of trying to build the code for deleting the data in B-L, and N-AI, and
de-highlighting the cells in N-AI. So far, I have the following:

Sub Delete_Data()
Dim rng As Range

Set rng = Cells(ActiveCell.Row, "A")
.Offset(0, 1).Resize(, 11).Selection.ClearContents
Selection.Interior.ColorIndex = x1None
.Offset(0, 13).Resize(, 21).Selection.ClearContents
Selection.Interior.ColorIndex = x1None
End Sub

Please look this over, if you don't mind, and tell me where I'm off. Again,
I would like to start & end in Column A.

Thanks so much for all your help - You're a godsend!
Sandi
"Jim Thomlinson" wrote:

VB will automatically place a line between sub procedures so you don't need
to worry about it... To see what I mean just type in

Sub Test

into an existing module and VB will automatically add "End Sub" and place a
line between it and other existing subs.

To remove a module right click on the Module and select "Remove Module?".
You will be prompted "Do you wnat to export... " . Just select No.
--
HTH...

Jim Thomlinson


"RUSH2CROCHET" wrote:

Jim:

Thanks - I feel smarter already! :-)

If you don't mind, perhaps just a little more guidance?! The "Sub
Contract_C" is the "macro name" I assigned when using the macro recorder. In
VB, I note that it appears in the "Declaration" field, so that you know what
sub procedure you are editing. If you record subsequent macros, there is a
line separating the macro code, however, If writing additional declarations
using Visual Basic, is it a "Break" that you enter to get the line separating
the different sub procedures, or does VB recognize a new sub procedure by the
formatting of the beginning statement? For example...
Sub Contract_C()
Dim rng As Range

Sub Contract_N()
Dim Rng As Range

Also, how would you delete an "empty" module?

Thanks once again for all your help!
Sandi

"Jim Thomlinson" wrote:

You are correct. The first thing my code does is it sets a range object
(similar to activecell) to whatever row you are on and column J. That is my
reference point.
I add the C to this spot and format the font. Next I define a range that is
5 columns to the right and resized 0 rows and 2 columns larger. It changes
the interior colour of that defined range to 35. Same deal with the next line
of code...

Things to note are that I do not move the active cell and I do not select
any cells. I use my range object instead. This makes the code more compact
and efficient while avoiding having the cursor flying around the screen.
--
HTH...

Jim Thomlinson


"RUSH2CROCHET" wrote:

THANK YOU, THANK YOU! You are exactly correct! That is precisely what I
need. As I don't think you want to write all my code, and I want to further
my code knowledge, I have another question.

I understand the .offset(0, 5) to be "go 5 columns to the right... but, what
exactly is Resize(, 2).
I'm thinking perhaps the (, 2) indicates to select the current plus next cell.

I will need to know this as one of my other contract types requires me to
color N-AI. In this case I would change my .offset(0, 4) to start in column
M, and then .Resize(, 22) to end in column AI.

Please confirm if my thoughts are correct.

Thanks again!
Sandi

"RUSH2CROCHET" wrote:

I hope my subject line is appropriate to the request....
Running Excel 2003, I have a spreadsheet which tracks new contract
opportunities for our company. There are many stages involved in the
contract process, and many people involved. Each contract is listed on a
single line, and column descriptions follow:
A=Blank
B-L=General information, such as Dealer Information, Acct. Exec Name & #,
Acct Rep, Type of Location, Date Requested, and so on.
M=Blank
N-AH=Contract Stages, where user enters dates of stage completion.

There are (9) different request types, which are entered into Column J. I
need to color in/highlight different cells within the N-AH range, dependent
on the Contract Type entered in Column J. This is to indicate need for
information in those columns. For example, for contract type C, I need to
highlight O:P, & W:X on the respective row. With my cursor in column B, I
recorded the following macro for"contract type C":

Sub Contract_C()
'
' Contract_C Macro
' Macro recorded 9/12/2007 by C11163 To highlight Contract Tracker cells for
Type C.
'
' Keyboard Shortcut: Ctrl+Shift+D
'
ActiveCell.Offset(0, 8).Range("A1").Select
ActiveCell.FormulaR1C1 = "C"
With ActiveCell.Characters(Start:=1, Length:=1).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
ActiveCell.Offset(0, 5).Range("A1,A1:B1").Select
ActiveWindow.SmallScroll ToRight:=4
ActiveCell.Range("A1,A1:B1,I1:J1").Select
ActiveCell.Offset(0, 8).Range("A1").Activate
With Selection.Interior
.ColorIndex = 35
.Pattern = xlSolid
End With
ActiveCell.Offset(0, -21).Range("A1").Select
End Sub

The problem is, I want to be able to enter the "C" (or other designation),
into column J, regardless of where on the particular row I am at, and color
in cells O:P, W:X on same row, and then return to column K.

I apologize for the lengthy post, however I am still new at macros & all
help is appreciated! If this would be easier covered off-line, I am amenable
to that also.

TIA!
Sandi

Precisely correct.... & exactly what I needed. I can't say thank you
enough!! One of these days, I need to take a VB class, so I don't have to be
such a pest ;-)

Have a great weekend!
Sandi

"Jim Thomlinson" wrote:

Selection will be reference the selected cell. You are wanting to work with
you range object and not the selection. Give this a whirl...

Sub Delete_Data()
Dim rng As Range

Set rng = Cells(ActiveCell.Row, "A")
with rng
.Offset(0, 1).Resize(, 11).ClearContents
.Offset(0, 1).Resize(, 11).Interior.ColorIndex = xlNone
.Offset(0, 13).Resize(, 21).ClearContents
.Offset(0, 13).Resize(, 21).Interior.ColorIndex = xlNone
end with
End Sub
--
HTH...

Jim Thomlinson


"RUSH2CROCHET" wrote:

Thanks for confirming that! Actually between my last post, and your
response, I was able to tested, and found that VB placed the line in for me.

I have all my code written, for the differing scenarios, and they work like
a champ, thanks to all your help & guidance! I am in the process currently
of trying to build the code for deleting the data in B-L, and N-AI, and
de-highlighting the cells in N-AI. So far, I have the following:

Sub Delete_Data()
Dim rng As Range

Set rng = Cells(ActiveCell.Row, "A")
.Offset(0, 1).Resize(, 11).Selection.ClearContents
Selection.Interior.ColorIndex = x1None
.Offset(0, 13).Resize(, 21).Selection.ClearContents
Selection.Interior.ColorIndex = x1None
End Sub

Please look this over, if you don't mind, and tell me where I'm off. Again,
I would like to start & end in Column A.

Thanks so much for all your help - You're a godsend!
Sandi
"Jim Thomlinson" wrote:

VB will automatically place a line between sub procedures so you don't need
to worry about it... To see what I mean just type in

Sub Test

into an existing module and VB will automatically add "End Sub" and place a
line between it and other existing subs.

To remove a module right click on the Module and select "Remove Module?".
You will be prompted "Do you wnat to export... " . Just select No.
--
HTH...

Jim Thomlinson


"RUSH2CROCHET" wrote:

Jim:

Thanks - I feel smarter already! :-)

If you don't mind, perhaps just a little more guidance?! The "Sub
Contract_C" is the "macro name" I assigned when using the macro recorder. In
VB, I note that it appears in the "Declaration" field, so that you know what
sub procedure you are editing. If you record subsequent macros, there is a
line separating the macro code, however, If writing additional declarations
using Visual Basic, is it a "Break" that you enter to get the line separating
the different sub procedures, or does VB recognize a new sub procedure by the
formatting of the beginning statement? For example...
Sub Contract_C()
Dim rng As Range

Sub Contract_N()
Dim Rng As Range

Also, how would you delete an "empty" module?

Thanks once again for all your help!
Sandi

"Jim Thomlinson" wrote:

You are correct. The first thing my code does is it sets a range object
(similar to activecell) to whatever row you are on and column J. That is my
reference point.
I add the C to this spot and format the font. Next I define a range that is
5 columns to the right and resized 0 rows and 2 columns larger. It changes
the interior colour of that defined range to 35. Same deal with the next line
of code...

Things to note are that I do not move the active cell and I do not select
any cells. I use my range object instead. This makes the code more compact
and efficient while avoiding having the cursor flying around the screen.
--
HTH...

Jim Thomlinson


"RUSH2CROCHET" wrote:

THANK YOU, THANK YOU! You are exactly correct! That is precisely what I
need. As I don't think you want to write all my code, and I want to further
my code knowledge, I have another question.

I understand the .offset(0, 5) to be "go 5 columns to the right... but, what
exactly is Resize(, 2).
I'm thinking perhaps the (, 2) indicates to select the current plus next cell.

I will need to know this as one of my other contract types requires me to
color N-AI. In this case I would change my .offset(0, 4) to start in column
M, and then .Resize(, 22) to end in column AI.

Please confirm if my thoughts are correct.

Thanks again!
Sandi

"RUSH2CROCHET" wrote:

I hope my subject line is appropriate to the request....
Running Excel 2003, I have a spreadsheet which tracks new contract
opportunities for our company. There are many stages involved in the
contract process, and many people involved. Each contract is listed on a
single line, and column descriptions follow:
A=Blank
B-L=General information, such as Dealer Information, Acct. Exec Name & #,
Acct Rep, Type of Location, Date Requested, and so on.
M=Blank
N-AH=Contract Stages, where user enters dates of stage completion.

There are (9) different request types, which are entered into Column J. I
need to color in/highlight different cells within the N-AH range, dependent
on the Contract Type entered in Column J. This is to indicate need for
information in those columns. For example, for contract type C, I need to
highlight O:P, & W:X on the respective row. With my cursor in column B, I
recorded the following macro for"contract type C":

Sub Contract_C()
'
' Contract_C Macro
' Macro recorded 9/12/2007 by C11163 To highlight Contract Tracker cells for
Type C.
'
' Keyboard Shortcut: Ctrl+Shift+D
'
ActiveCell.Offset(0, 8).Range("A1").Select
ActiveCell.FormulaR1C1 = "C"
With ActiveCell.Characters(Start:=1, Length:=1).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
ActiveCell.Offset(0, 5).Range("A1,A1:B1").Select
ActiveWindow.SmallScroll ToRight:=4
ActiveCell.Range("A1,A1:B1,I1:J1").Select
ActiveCell.Offset(0, 8).Range("A1").Activate
With Selection.Interior
.ColorIndex = 35
.Pattern = xlSolid
End With
ActiveCell.Offset(0, -21).Range("A1").Select
End Sub

The problem is, I want to be able to enter the "C" (or other designation),
into column J, regardless of where on the particular row I am at, and color
in cells O:P, W:X on same row, and then return to column K.

I apologize for the lengthy post, however I am still new at macros & all
help is appreciated! If this would be easier covered off-line, I am amenable
to that also.

TIA!
Sandi