Conditional Formatting in 2 different columns put into a macro

[D_Licious]

hello everyone,

so I run a macro where I auto-format cells f8:f100 to be conditionally formatted to turn yellow if the D value in the same row is greater than 10,000 and the e column in the same row is greater than 5. The formula is =and(abs(d8)>10000,abs(e8)>5). After this, I do the same think to column K, except this time the conditional highlight is dependent on the values of I & J, instead of D & E. At the end of recording the macro, I go into the "conditional formatting Rules Manager" and make sure the "applies to" box says "=f8:f100" for the column F rule and "=k8:k100" for the 2nd rule.


If I close the document and use it later, repopulate numbers and run the conditional format recorded macro, the K column still formats K8:K100 the way it is supposed to. Now however, in the "applies to" box, it says "=$K$8:$K$100". (I didn't initially put the absolute reference dollar signs in there when I recorded the macro).

And then the column F conditional format is very messed up. the formula itself is just like the one for K, dependent on I and J...... but I need the F conditional format to be dependent on D and E. Furthermore, the "applies to" box for the F format just says "=$K$8", which has nothing to do with what I recorded during the macro creation...... it needs to be "=f8:f100".

Does anybody know what it causing this?

any help is greatly appreciated!!

thank you!

 

[BQardi]

Can you post the code from the macro?

 

[D_Licious]

yep, here it is:

Sub FORMAT()
'
' FORMAT Macro
'
'
Columns("F:F").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.ColumnWidth = 39
With Selection
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("F5").Select
ActiveCell.FormulaR1C1 = "PTD Comments"
Columns("K:K").Select
Selection.Delete Shift:=xlToLeft
Columns("K:K").Select
Selection.ColumnWidth = 39
With Selection
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("K5").Select
ActiveCell.FormulaR1C1 = "YTD Comments"
Range("A1:K1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Range("A2:K2").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Range("A3:K3").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Range("A4:K4").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Cells.Select
Selection.Replace What:="N/A", Replacement:="-100", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("H12").Select
ActiveWindow.SmallScroll ToRight:=-5
Range("F8").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(ABS(D8)>10000,ABS(E8)>5)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Range("K8").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(ABS(I8)>12500,ABS(J8)>5)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Cells.FormatConditions.Delete
Range("F8:F400").Select
Range("K8").Activate
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(ABS(I8)>10000,ABS(J8)>5)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Range("K8:K400").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(ABS(I8)>12500,ABS(J8)>5)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
ActiveWindow.SmallScroll ToRight:=-5
Columns("A:A").EntireColumn.AutoFit
Columns("B:B").EntireColumn.AutoFit
Columns("C:C").EntireColumn.AutoFit
Columns("D:D").EntireColumn.AutoFit
Columns("E:E").EntireColumn.AutoFit
Columns("G:G").EntireColumn.AutoFit
Columns("H:H").EntireColumn.AutoFit
Columns("I:I").EntireColumn.AutoFit
Columns("J:J").EntireColumn.AutoFit
End Sub

In addition to the issue I mentioned, there are also a lot of other small monotonous formatting tasks.

Let me know if you have any questions,
thank you!

 

[BQardi]

Try to delete the line I have highlighted:

[COLOR=#333333]Sub FORMAT()[/COLOR]
[COLOR=#333333]'[/COLOR]
[COLOR=#333333]' FORMAT Macro[/COLOR]
[COLOR=#333333]'[/COLOR]
[COLOR=#333333]'[/COLOR]
[COLOR=#333333]Columns("F:F").Select[/COLOR]
[COLOR=#333333]Application.CutCopyMode = False[/COLOR]
[COLOR=#333333]Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove[/COLOR]
[COLOR=#333333]Selection.ColumnWidth = 39[/COLOR]
[COLOR=#333333]With Selection[/COLOR]
[COLOR=#333333].VerticalAlignment = xlCenter[/COLOR]
[COLOR=#333333].WrapText = True[/COLOR]
[COLOR=#333333].Orientation = 0[/COLOR]
[COLOR=#333333].AddIndent = False[/COLOR]
[COLOR=#333333].IndentLevel = 0[/COLOR]
[COLOR=#333333].ShrinkToFit = False[/COLOR]
[COLOR=#333333].ReadingOrder = xlContext[/COLOR]
[COLOR=#333333].MergeCells = False[/COLOR]
[COLOR=#333333]End With[/COLOR]
[COLOR=#333333]Range("F5").Select[/COLOR]
[COLOR=#333333]ActiveCell.FormulaR1C1 = "PTD Comments"[/COLOR]
[COLOR=#333333]Columns("K:K").Select[/COLOR]
[COLOR=#333333]Selection.Delete Shift:=xlToLeft[/COLOR]
[COLOR=#333333]Columns("K:K").Select[/COLOR]
[COLOR=#333333]Selection.ColumnWidth = 39[/COLOR]
[COLOR=#333333]With Selection[/COLOR]
[COLOR=#333333].VerticalAlignment = xlCenter[/COLOR]
[COLOR=#333333].WrapText = True[/COLOR]
[COLOR=#333333].Orientation = 0[/COLOR]
[COLOR=#333333].AddIndent = False[/COLOR]
[COLOR=#333333].IndentLevel = 0[/COLOR]
[COLOR=#333333].ShrinkToFit = False[/COLOR]
[COLOR=#333333].ReadingOrder = xlContext[/COLOR]
[COLOR=#333333].MergeCells = False[/COLOR]
[COLOR=#333333]End With[/COLOR]
[COLOR=#333333]Range("K5").Select[/COLOR]
[COLOR=#333333]ActiveCell.FormulaR1C1 = "YTD Comments"[/COLOR]
[COLOR=#333333]Range("A1:K1").Select[/COLOR]
[COLOR=#333333]With Selection[/COLOR]
[COLOR=#333333].HorizontalAlignment = xlCenter[/COLOR]
[COLOR=#333333].VerticalAlignment = xlCenter[/COLOR]
[COLOR=#333333].Orientation = 0[/COLOR]
[COLOR=#333333].AddIndent = False[/COLOR]
[COLOR=#333333].IndentLevel = 0[/COLOR]
[COLOR=#333333].ShrinkToFit = False[/COLOR]
[COLOR=#333333].ReadingOrder = xlContext[/COLOR]
[COLOR=#333333].MergeCells = False[/COLOR]
[COLOR=#333333]End With[/COLOR]
[COLOR=#333333]Selection.Merge[/COLOR]
[COLOR=#333333]Range("A2:K2").Select[/COLOR]
[COLOR=#333333]With Selection[/COLOR]
[COLOR=#333333].HorizontalAlignment = xlCenter[/COLOR]
[COLOR=#333333].VerticalAlignment = xlCenter[/COLOR]
[COLOR=#333333].Orientation = 0[/COLOR]
[COLOR=#333333].AddIndent = False[/COLOR]
[COLOR=#333333].IndentLevel = 0[/COLOR]
[COLOR=#333333].ShrinkToFit = False[/COLOR]
[COLOR=#333333].ReadingOrder = xlContext[/COLOR]
[COLOR=#333333].MergeCells = False[/COLOR]
[COLOR=#333333]End With[/COLOR]
[COLOR=#333333]Selection.Merge[/COLOR]
[COLOR=#333333]Range("A3:K3").Select[/COLOR]
[COLOR=#333333]With Selection[/COLOR]
[COLOR=#333333].HorizontalAlignment = xlCenter[/COLOR]
[COLOR=#333333].VerticalAlignment = xlCenter[/COLOR]
[COLOR=#333333].Orientation = 0[/COLOR]
[COLOR=#333333].AddIndent = False[/COLOR]
[COLOR=#333333].IndentLevel = 0[/COLOR]
[COLOR=#333333].ShrinkToFit = False[/COLOR]
[COLOR=#333333].ReadingOrder = xlContext[/COLOR]
[COLOR=#333333].MergeCells = False[/COLOR]
[COLOR=#333333]End With[/COLOR]
[COLOR=#333333]Selection.Merge[/COLOR]
[COLOR=#333333]Range("A4:K4").Select[/COLOR]
[COLOR=#333333]With Selection[/COLOR]
[COLOR=#333333].HorizontalAlignment = xlCenter[/COLOR]
[COLOR=#333333].VerticalAlignment = xlCenter[/COLOR]
[COLOR=#333333].Orientation = 0[/COLOR]
[COLOR=#333333].AddIndent = False[/COLOR]
[COLOR=#333333].IndentLevel = 0[/COLOR]
[COLOR=#333333].ShrinkToFit = False[/COLOR]
[COLOR=#333333].ReadingOrder = xlContext[/COLOR]
[COLOR=#333333].MergeCells = False[/COLOR]
[COLOR=#333333]End With[/COLOR]
[COLOR=#333333]Selection.Merge[/COLOR]
[COLOR=#333333]Cells.Select[/COLOR]
[COLOR=#333333]Selection.Replace What:="N/A", Replacement:="-100", LookAt:=xlPart, _[/COLOR]
[COLOR=#333333]SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _[/COLOR]
[COLOR=#333333]ReplaceFormat:=False[/COLOR]
[COLOR=#333333]Range("H12").Select[/COLOR]
[COLOR=#333333]ActiveWindow.SmallScroll ToRight:=-5[/COLOR]
[COLOR=#333333]Range("F8").Select[/COLOR]
[COLOR=#333333]Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _[/COLOR]
[COLOR=#333333]"=AND(ABS(D8)>10000,ABS(E8)>5)"[/COLOR]
[COLOR=#333333]Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority[/COLOR]
[COLOR=#333333]With Selection.FormatConditions(1).Interior[/COLOR]
[COLOR=#333333].PatternColorIndex = xlAutomatic[/COLOR]
[COLOR=#333333].Color = 65535[/COLOR]
[COLOR=#333333].TintAndShade = 0[/COLOR]
[COLOR=#333333]End With[/COLOR]
[COLOR=#333333]Selection.FormatConditions(1).StopIfTrue = False[/COLOR]
[COLOR=#333333]Range("K8").Select[/COLOR]
[COLOR=#333333]Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _[/COLOR]
[COLOR=#333333]"=AND(ABS(I8)>12500,ABS(J8)>5)"[/COLOR]
[COLOR=#333333]Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority[/COLOR]
[COLOR=#333333]With Selection.FormatConditions(1).Interior[/COLOR]
[COLOR=#333333].PatternColorIndex = xlAutomatic[/COLOR]
[COLOR=#333333].Color = 65535[/COLOR]
[COLOR=#333333].TintAndShade = 0[/COLOR]
[COLOR=#333333]End With[/COLOR]
[COLOR=#333333]Selection.FormatConditions(1).StopIfTrue = False[/COLOR]
[COLOR=#333333]Cells.FormatConditions.Delete[/COLOR]
[COLOR=#333333]Range("F8:F400").Select[/COLOR]
[COLOR=#ff0000]Range("K8").Activate [/COLOR][COLOR=#008000]'Try to delete this line[/COLOR]
[COLOR=#333333]Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _[/COLOR]
[COLOR=#333333]"=AND(ABS(I8)>10000,ABS(J8)>5)"[/COLOR]
[COLOR=#333333]Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority[/COLOR]
[COLOR=#333333]With Selection.FormatConditions(1).Interior[/COLOR]
[COLOR=#333333].PatternColorIndex = xlAutomatic[/COLOR]
[COLOR=#333333].Color = 65535[/COLOR]
[COLOR=#333333].TintAndShade = 0[/COLOR]
[COLOR=#333333]End With[/COLOR]
[COLOR=#333333]Selection.FormatConditions(1).StopIfTrue = False[/COLOR]
[COLOR=#333333]Range("K8:K400").Select[/COLOR]
[COLOR=#333333]Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _[/COLOR]
[COLOR=#333333]"=AND(ABS(I8)>12500,ABS(J8)>5)"[/COLOR]
[COLOR=#333333]Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority[/COLOR]
[COLOR=#333333]With Selection.FormatConditions(1).Interior[/COLOR]
[COLOR=#333333].PatternColorIndex = xlAutomatic[/COLOR]
[COLOR=#333333].Color = 65535[/COLOR]
[COLOR=#333333].TintAndShade = 0[/COLOR]
[COLOR=#333333]End With[/COLOR]
[COLOR=#333333]Selection.FormatConditions(1).StopIfTrue = False[/COLOR]
[COLOR=#333333]ActiveWindow.SmallScroll ToRight:=-5[/COLOR]
[COLOR=#333333]Columns("A:A").EntireColumn.AutoFit[/COLOR]
[COLOR=#333333]Columns("B:B").EntireColumn.AutoFit[/COLOR]
[COLOR=#333333]Columns("C:C").EntireColumn.AutoFit[/COLOR]
[COLOR=#333333]Columns("D:D").EntireColumn.AutoFit[/COLOR]
[COLOR=#333333]Columns("E:E").EntireColumn.AutoFit[/COLOR]
[COLOR=#333333]Columns("G:G").EntireColumn.AutoFit[/COLOR]
[COLOR=#333333]Columns("H:H").EntireColumn.AutoFit[/COLOR]
[COLOR=#333333]Columns("I:I").EntireColumn.AutoFit[/COLOR]
[COLOR=#333333]Columns("J:J").EntireColumn.AutoFit[/COLOR]
[COLOR=#333333]End Sub[/COLOR]