I need help setting up a Conditional Format formula using VBA.
Currently I fill a lot of rows of data with a simple formula using
this code:
There are several versions of this code in the module, each one
covering a unique data type ‘QD indicates the data type.

Sub condFormatingDEV()

'QD
Dim qdNm As Name
Dim qdNmRng As Range
Dim qdNmRngA As Range
Dim qdNmRngB As Range
Dim qdNmRngC As Range

For Each qdNm In ActiveSheet.Names ' Iterate through each
element.
On Error Resume Next
Set qdNmRngB = qdNm.RefersToRange
On Error GoTo 0
If Not qdNmRngB Is Nothing Then
Set qdNmRngA = Cells.Find(what:="QD", _
After:=ActiveCell, _
LookIn:=xlValues, _
lookat:=xlPart, _
searchorder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

On Error Resume Next
If Not qdNmRngA Is Nothing Then
Range(qdNmRngA).Select
Selection.FormatConditions.Delete

'UCL
Selection.FormatConditions.Add
Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=" & Selection.End
(xlDown).Offset(11, 0).Address
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.Strikethrough = False
End With
Selection.FormatConditions
(1).Interior.ColorIndex = 22

End If
End If
Next

End Sub

The end result is a formula like =$K$76. The criteria is in the same
column but a few spaces below so the offset command work nicely.
Now I have two criteria that can be used dependant on the value of a
cell in the same row. The cell in the row is always in column “C”
which has a heading of “Type”.

Manually entering the formula in the Conditional Format wizard I enter
"=IF($C5="HL",K$25,$K$22)". Then copy/paste in to the remaining cells
in the column using the format painter. I also copy/paste the formats
to any other column that contains QD data and that works as well. No
I want to build the formula using VBA

Locating the two criteria’s can be done as I did before using
Selection.End(xlDown).Offset(#, 0) .address twice. How do I do I call
out column C of the current row?

Thanks
Robert

For what its worth I hashed out the easy part of the formula but still
need to replace "C5" with something that references the intersection
of column C with the current row.


Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater,
_
Formula1:="=IF(" & "C5" & "=" & """HL"""
& "," & _
Selection.End(xlDown).Offset(17,
0).Address _
& "," & _
Selection.End(xlDown).Offset(14,
0).Address _
& ")"

With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.Strikethrough = False
End With
Selection.FormatConditions
(1).Interior.ColorIndex = 22