# Thread: Using Offset (General Coding Problem) (Excel 97 SR2 VB )

1. ## Using Offset (General Coding Problem) (Excel 97 SR2 VB )

This is my first post so I would like to state thank you for this awesome resource!! I'm sure you can tell from my attached code that I'm a beginner. If there is another method/approach that will work better than please let me know. My code works until I get to an area in my worksheet where I need to assign the format based on the value in another column. I need it to be a 1:1 comparison, in other words if L13 is equal to 1 than K13 should be assigned a color format equal to green. If there is any additional information needed please let me know but I'm up against a deadline. Thanks in advance for any assistance provided!!

2. ## Re: Using Offset (General Coding Problem) (Excel 97 SR2 VB )

You can use Conditional Formatting to achieve what you describe. Insteda of setting the condition based on the Cell value, you can set it based on a formula.

To change the color of Cell K13 based on the value of L13 you could use the following conditional formatting ;

Condition 1
Formula Is =\$L13=1

To change the condition to respond to a formula, click on the Cell Value Is box, and you should see Formula Is, and select that, enter the above formula and you should be ok.

You can then copy and paste the formatting to any other cells in column K that you want the same. I am attaching your workbook with the format applied to K13

Hope that helps

Andrew C

3. ## Re: Using Offset (General Coding Problem) (Excel 97 SR2 VB )

Thanks Andrew for the reply!! I'm quite familiar with Excel but a VB newbie. Please look at the procedure attached in module 1. I need to be able to look at the entire range and select the corresponding cell and do this via VB. This is an Invision Layout (PeopleSoft) that can change dynamically depending on the criteria requested so think of my file as a shell where the procedure will run against the output. I hope that wasn't confusing. Thanks!!

4. ## Re: Using Offset (General Coding Problem) (Excel 97 SR2 VB )

I would suggest that you do not have your macro apply the color by conditional formatting, and just color the cell if a certain value is found, otherwise set the color to none. the following code works on your worksheet, but only if I remove ' from L28 (a hidden row). If that is a problem it can be amended. <pre>Sub ColorCells()
Dim oCell As Range
If oCell.Value = 1 Then
oCell.Offset(0, -1).Interior.ColorIndex = 4
Else
oCell.Offset(0, -1).Interior.ColorIndex = vbNone
End If
Next
End Sub</pre>

See if you can use that to solve the problem

Andrew

5. ## Re: Using Offset (General Coding Problem) (Excel 97 SR2 VB )

You are just too quick on the draw, pardner! <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

6. ## Re: Using Offset (General Coding Problem) (Excel 97 SR2 VB )

<font color=red>Edits in red</font color=red>

He is looking for something like:

Dim oCell As Range
For Each oCell In Selection
<font color=red>If oCell.Value <> "" Then</font color=red> <font color=448800>' Cell L28, last in the range, contains a single apostrophe</font color=448800>
If oCell.Value = 3 Then
<font color=red>oCell</font color=red>.Offset(0, -1).Interior.ColorIndex = 5
ElseIf oCell.Value = 2 Then
<font color=red>oCell</font color=red>.Offset(0, -1).Interior.ColorIndex = 6
ElseIf oCell.Value = 1 Then
<font color=red>oCell</font color=red>.Offset(0, -1).Interior.ColorIndex = 4
End If
<font color=red>End If</font color=red>
Next oCell

7. ## Re: Using Offset (General Coding Problem) (Excel 97 SR2 VB )

John,

When I posted the above I was not aware you had come up with an answer. I suppose the hidden cell (or it's contents) caused you problems.

Andrew

8. ## Re: Using Offset (General Coding Problem) (Excel 97 SR2 VB )

I think you beat me to a complete working answer. The apostophe was giving me fits. I was using this as a learning exercise, beating my head against wall while you no doubt wrote your version in seven seconds. <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>

9. ## Re: Using Offset (General Coding Problem) (Excel 97 SR2 VB )

Just for the record I noticed you used If Trades_OOB_Grading = 2 in your code, which would have caused problems. What you should have used was If Range("Trades_OOB_Grading").Value = 2. Even then that would only work if there was a single cell in the range. VBA would just view Trades_OOB_Grading as an undeclared variable with no value assigned to it, and hence it would evaluate to zero.

Andrew

10. ## Re: Using Offset (General Coding Problem) (Excel 97 SR2 VB )

John & Andrew,

I just wanted to thank you both publicly for your assistance. I can't tell you how excited I was to come in this morning and find solutions/suggestions to my challenge. I will post my findings here!!

11. ## Re: Using Offset (General Coding Problem) (Excel 97 SR2 VB )

Andrew & John,

Just curious. Is it not possible to do what I'm trying to do via Conditional Formatting? The reason that I ask is because with the current approach the formatting becomes static. The way my users will be working with the document is that the grading will not be there until after the document has been processed so if the grading is not present or it changes the corresponding formatting doesn't change. When I use the cond. formatting approach this of course is not a problem. Thanks again for any assistance that you can provide.

12. ## Re: Using Offset (General Coding Problem) (Excel 97 SR2 VB )

See <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Cat=&Board=xl&Number=54017&page=1& view=collapsed&sb=5&o=0&part=>This Thread</A> for some ideas.

13. ## Re: Using Offset (General Coding Problem) (Excel 97 SR2 VB )

The code posted could be adopted to apply conditional formatting, but that formatting is then static, and so I really cannot see the point of doing it via a macro, when you can apply it manually. If the value used to determine the formatting changes you will have to run the macro again, and unless it picks the value up automatically, you would have to amend it as well.

So if the conditions do not alter I would recommend using manual formatting. If the conditions do change I would suggest that you use a macro which can pick the new value up from a range. The macro could be triggered to run should any changes be made to that range.

Andrew

14. ## Re: Using Offset (General Coding Problem) (Excel 97 SR2 VB )

Thanks Andrew!! I was thinking the same thing regarding a macro. How would you go about kicking off the macro if data is entered in the range? I know in Access there are features where you can have something kick off on event (such as someone clicking a button, etc.) Is there a way to do this in Excel?

15. ## Re: Using Offset (General Coding Problem) (Excel 97 SR2 VB )

There is a Worksheet_SelectionChange event which can be used to trigger code should the value of a range change. There are other events also, including clicking buttons, graphics etc.

However it would be good to know if the criteria on which the conditional formatting is based is likely to change. For example in the scenario you used above, is it likely that you will need to change the criteria (i.e. the value of L13) that changes the color of K13 to green (from 1 to some other number).

Andrew

Page 1 of 3 123 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•