Conditional formatting with a relative cell.

**roontoon** · 05-09-2012, 10:29 AM

Hi,

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.

Sub ConditionalFormat()
'
'
'
    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

**roontoon** · 05-09-2012, 10:43 AM

I had another thought about this....

The reason that I am even trying to create this macro is that this has to be generic to the group of cells selected which have the same number of rows and columns. This range does have formulas and after I had the conditional formatting working like I wanted to work I could not select the range and replicate it by dragging the bottom right corner of the selection. If there is a way to do that I would also like to know how to do it. Thanks again.