About Us

Roontoon

New to the forum and I have a question regarding the macro below. I am trying to create a macro that is generic in nature to be used in a financial work sheet but on any group of cells in the worksheet. I need the entire range to be generic which seems to be working but I am having a problem trying to figure out how to change the formula on line 3 to be generic. The cell in question is three rows down and 16 columns from the origination point. I am somewhat of a novice with actual editing of macros so please be kind if this question is obvious. Thanks for your help.

Code:

    ActiveSheet.Range(ActiveCell.Offset(3, 0), ActiveCell.Offset(0, 29)).Select
    ActiveWindow.SmallScroll ToRight:=-6
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$Q$114<0"   <--------- This is my question.
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ColorIndex = xlAutomatic
    End With
    Selection.FormatConditions(1).StopIfTrue = False
End Sub

joeu2004[_2_]

"Roontoon" wrote:
The cell in question is three rows down and 16 columns
from the origination point.

"16 columns __from__" does not tell us to the left or right. I will presume
16 to the right, since you are less likely to encounter boundary conditions.

"Roontoon" wrote:
Selection.FormatConditions.Add Type:=xlExpression, _
Formula1:="=$Q$114<0"

I'm not familiar with FormatConditions.Add per se. Based on your syntax,
the following should work for su

Formula1:="=" & Selection.Offset(3,16).Address & "<0"

But the following would be better if it works:

Formula1:="=R[3]C[16]<0"

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
VBA problem for forumla with relative reference	Joanne	Excel Worksheet Functions	2	December 9th 08 01:20 PM
i've tried every thing relative reference problem.	Tomkat743	Excel Programming	1	February 3rd 07 09:02 AM
Using an offset formula for the reference in a relative reference	Cuda	Excel Worksheet Functions	6	November 15th 06 05:12 PM
problem with relative reference	Herman	Excel Programming	1	May 18th 05 07:14 PM
relative reference	Charlie	New Users to Excel	3	December 3rd 04 07:22 PM

Menu

About Us