Formula Assistance - Relative vs. Constant

[Guest]

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, & 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, 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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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