I have applied the duplicate conditional format to columns AB, AD, AR, AT, BH & BJ across a worksheet with around 1500 rows, however it is also highlighting the blanks.
I have tried to create an additional conditional format of changing any of the blanks to a white cell colour, as I can't find a way to remove the conditional format from the blank cells.
The only way I have found to highlight the cells white at the moment is to use the conditional formatting again, which works perfectly when I record the macro but not when I replay this as it turns either the whole column white, or leaves blank cells as red.
This is the vba code of the additional conditional format:
Sub Macro3()
Range("I:I,AB:AB,AD:AD,AR:AR,AT:AT,BH:BH,BJ:BJ").Select
Range("BJ1").Activate
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=LEN(TRIM(BJ1))=0"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
End Sub
Help is greatly appreciated as I've spent far too long trying to find a work around!
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=LEN(TRIM(i1))=0"
– brettdj