Conditional Formating (how to use Offset() in cell reference)

[Dennis]

Using XL 2003 & 97

How do enter into VBA code; the cell Offset R-12,C into
the Contitional Formatting below? (I want the "not equal to" referrence a
cell 12 rows above in the same column)

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlNotEqual,
Formula1:="=$J$6397"

TIA

Dennis

 

[Dave Peterson]

This might help you get to the next step:

Option Explicit
Sub testme01()
With Selection
If .Row < 13 Then
'too high in the worksheet
Else
'remove any existing format first?????
.FormatConditions.Delete
.FormatConditions.Add Type:=xlCellValue, _
Operator:=xlNotEqual, _
Formula1:="=" & .Cells(1).Offset(-12, 0).Address(0, 0)
.FormatConditions(1).Interior.ColorIndex = 19
End If
End With

End Sub

 

[Dennis]

Appreciate your time and knowledge.

This might help you get to the next step:

Option Explicit
Sub testme01()
With Selection
If .Row < 13 Then
'too high in the worksheet
Else
'remove any existing format first?????
.FormatConditions.Delete
.FormatConditions.Add Type:=xlCellValue, _
Operator:=xlNotEqual, _
Formula1:="=" & .Cells(1).Offset(-12, 0).Address(0, 0)
.FormatConditions(1).Interior.ColorIndex = 19
End If
End With

End Sub