How to get a cell reference in conditional formatting formula using VBA

[Yousif13399]

Hello
Os:windows 11/excel 2016

I need to highlight the values that are equal to D1 in the range of A1 to A2, but I need to do it in a Macro VBA and I need to use cell reference

I recorded the process using record a macro and this is the original code:

So
1st row: I changed the range to: Activecell,Activecell.offset(1,0) (this will select the current cell and the one below it as the range for the macro and this works perfectly
3rd row: I changed the formulal equal to: Activecell.offset(0,3)

But instead of excel writing the cell reference in the formula it's copying the exact words of Activecell.offset(0,3)

how can I get $D$3 instead of Activecell.offset(0,3)?

 

[kevin9999]

If you changed your original code to:

Formula1:="=$D$3"

does that not give you what you want?

 

[Yousif13399]

If you changed your original code to:

Formula1:="=$D$3"

does that not give you what you want?

It does for one line, but in my project I have thousands of lines so I just want to go to the corresponding row and initiate the macro, keeping D3 in the macro will always go to cell D3.

 

[Fluff]

try

Formula1 = ActiveCell.Offset(, 3).Address