Code to fill a column range with conditional formatting is using absolute reference

[frank_m]

When I manually enter the formula shown in this code into Cell B16
and then use the format painter to copy it down my column range it correctly uses relative references

But when I try to do it in code, it is incorrectly using an absolute reference.

What am I doing wrong?

Sample Workbook attached
Sub Add__Conditional_Formating()

ActiveSheet.Unprotect

Dim wks As Worksheet

Set wks = ActiveSheet

With wks

Lastrow = .[B65536].End(xlUp).Row

Set rng = .Range("B16:B" & Lastrow)

End With

Application.ScreenUpdating = False

Application.EnableEvents = False

rng.FormatConditions.Delete

With Range("B16")

.FormatConditions.Add Type:=xlExpression, Formula1:= _
"""=IF(ISNUMBER(--(SUBSTITUTE($B15,""""-"""",0))),IF(ISNUMBER(FIND(""""-"""",$B16)), --(SUBSTITUTE($B16,""""-"""",0)), $B16*100)> IF(ISNUMBER(FIND(""""-"""",$B15)), --(SUBSTITUTE($B15,""""-"""",0)),$B15*100))"""

.FormatConditions(1).Font.ColorIndex = 3

.Copy

End With

rng.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Application.CutCopyMode = False

'Range("G24").Select
Application.EnableEvents = True

Application.ScreenUpdating = True
End Sub Edit: Will it work if I use a loop to add the formula in each cell ?
* I did try that already with the same result, but if an expert thinks that is what's needed, I'll try again, as perhaps I implemented it wrong with my 1st try.

[shrivallabha]

Just an opinion, untested. Did you try changing this (in red):

.FormatConditions.Add Type:=xlExpression, Formula1:= _
"""=IF(ISNUMBER(--(SUBSTITUTE($B15,""""-"""",0))),IF(ISNUMBER(FIND(""""-"""",$B16)), --(SUBSTITUTE($B16,""""-"""",0)), $B16*100)> IF(ISNUMBER(FIND(""""-"""",$B15)), --(SUBSTITUTE($B15,""""-"""",0)),$B15*100))"""
To this:

.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=IF(ISNUMBER(--(SUBSTITUTE($B15,""""-"""",0))),IF(ISNUMBER(FIND(""""-"""",$B16)), --(SUBSTITUTE($B16,""""-"""",0)), $B16*100)> IF(ISNUMBER(FIND(""""-"""",$B15)), --(SUBSTITUTE($B15,""""-"""",0)),$B15*100))"

[Bob Phillips]

I agree with shrivallabha, but you need to go much further, you have far too many quotes in ther.

Use



"=IF(ISNUMBER(--(SUBSTITUTE($B15,""-"",0))),IF(ISNUMBER(FIND(""-"",$B16)), --(SUBSTITUTE($B16,""-"",0)), $B16*100)> IF(ISNUMBER(FIND(""-"",$B15)), --(SUBSTITUTE($B15,""-"",0)),$B15*100))"


As an aside, you should avoid hard-coding wherever possible, so don't use



Lastrow = .[B65536].End(xlUp).Row

but use



Lastrow = .Cells(.Rows.Count, "B").End(xlUp).Row

(I also removed that nasty [cell] syntax!).

[frank_m]

Hi shrivallabha, thanks for sharing your wisdom that is far above mine.

Hi Bob - Using the formula as you presented it, with fewer quote's (than what the macro recorder gave me), however it still resulted in absolute references, instead of relative.(when using it in my version of code) -- I googled a bit and found a piece of your code (I think at Mr Excel) and it works :thumb

Thanks

Sub Add_Conditional_Formating()

'This code works correctly

Dim Lastrow As Long
Dim i As Long

Dim wks As Worksheet

Set wks = ActiveSheet

With wks
Lastrow = ActiveSheet.Cells(.Rows.Count, "B").End(xlUp).Row
End With

Application.EnableEvents = False

Range("B16").Select 'I'm clueless, but without B16 selected I occasionally get incorrect references

For i = 1 To Lastrow - 1 'I'm also clueless as to why 16 To Lastrow won't work (16 is my 1st Row)
With Range("B" & i + 2)
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=IF(ISNUMBER(--(SUBSTITUTE($B15,""-"",0))),IF(ISNUMBER(FIND(""-"",$B16)), --(SUBSTITUTE($B16,""-"",0)), $B16*100)> IF(ISNUMBER(FIND(""-"",$B15)), --(SUBSTITUTE($B15,""-"",0)),$B15*100))"
.FormatConditions(1).Font.ColorIndex = 3
End With
Next i

Application.EnableEvents = True

End Sub