So, i'm writing a macro to take multiple CSV downloads and merge them to one worksheet so it can be audited. However, some of the data is incomplete so once the worksheet has been put together, i want to apply a conditional format to the range we have just created to highlight blank cells.


My code is:

Dim BaseWks as Worksheet
Dim Destrange as Range
Dim Lrow as Long
Dim ColCount as Long

LRow = BaseWks.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
ColCount = BaseWks.Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

' find last row of the merged tables
' find the last column of the merged tables
Destrange = BaseWks.Range(Cells(1, 1), Cells(LRow, ColCount))

With Destrange.FormatConditions.Add(xlCellValue, xlEqual, "") 
' fails with runtime error 5 invalid procedure or call
    .Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorAccent2
    .TintAndShade = 0.799981688894314
    .PatternTintAndShade = 0
End With