Dragging Conditional Formatting

[akshatk1991]

So i have a dozen tables and i want to format the colour of each column based on whether a certain cell in each column is greater than 0

So i copied the cells formula out above the table where it would be a number of 0, i then proceeded to conditionally format that column using a conditional formatting formula =$B$4>0

My issue is that i cannot drag this along the table, if i change the $ or remove them, the conditional formatting behaves differently. Is there any way to copy the formatting across the table, without manually reselecting and reapplying conditional formatting? Thanks!

 

[BeingYogi]

Have you tried


=B$4>0

 

[akshatk1991]

yes, when i do that, it misses out on certain cells, making the table look like a jigsaw puzzle haha, the only version it works with is =$b$4>0

 

[BeingYogi]

can you post some data samples

 

[akshatk1991]

This is the code, as you can see, i am manually moving forward, is there a way to do this faster?


Range("BK7:BK12,BK14:BK17,BK19:BK21,BK23,BK25:BK29").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$BK$5>0"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.Pattern = xlNone
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False

Range("BL7:BL12,BL14:BL17,BL19:BL21,BL23,BL25:BL29").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$BL$5>0"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.Pattern = xlNone
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False

Range("BM7:BM12,BM14:BM17,BM19:BM21,BM23,BM25:BM29").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$BM$5>0"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.Pattern = xlNone
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False