frank_m
02-19-2011, 03:58 PM
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.
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.