Exporting access query to VBA, conditional formatting allows .AddColorScale, but not .Add

Question

first time asking a question so apologies if I mess up somewhere. Usually I can find the answer on here, but not today!

I'm trying to add conditional formatting to an Excel sheet after using DoCmd.TransferSpreadsheet from an Access database query result. I can get .AddColorScale to work, but I want .Add xlCellValue - and I can't get it to work. Here's my code:

Sub excelExportOld()

excelName = InputBox("Name the file:")

nameofUser = CreateObject("WScript.Network").UserName

filePath = "C:\Users\" & nameofUser & "\Documents\Movers_" & excelName & ".xlsx"

DoCmd.TransferSpreadsheet TransferType:=acExport, SpreadsheetType:=acSpreadsheetTypeExcel12Xml, TableName:="Combined_movers", FileName:=filePath, HasFieldNames:=True

'open the created Excel doc
Set xlApp = CreateObject("Excel.Application")
With xlApp
    .Visible = True
    Set wkbk = .workbooks.Open(filePath)
End With


'format Excel doc
Set wks = wkbk.worksheets(1)
With wks

    'column width adjustments
    With .cells
        .Select
        .EntireColumn.AutoFit
    End With
    
    'format header
    .range("A1:T1").Interior.Color = RGB(217, 217, 217)
    
    'conditional format 'Moves' column
    With .range("P:P")
        .FormatConditions.AddColorScale ColorScaleType:=3
        .FormatConditions(1).ColorScaleCriteria(2).Type = xlConditionValueNumber
        .FormatConditions(1).ColorScaleCriteria(2).Value = 0
    End With
    With .range("Q:Q")
        .FormatConditions.AddColorScale ColorScaleType:=3
        .FormatConditions(1).ColorScaleCriteria(2).Type = xlConditionValueNumber
        .FormatConditions(1).ColorScaleCriteria(2).Value = 0
    End With
    With .range("R:R")
    '**everything works fine up until here - now I get a debug error on the next line**
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlNotEqual, Formula1:="=1"
    'I've also tried:
    '.FormatConditions.Add(1, 4, "=1") // and .Add(CellValue, xlNotEqual, "=1")
    'but it doesn't seem to like any of them

        .FormatConditions(1).Interior.Color = RGB(255, 0, 0)
    End With

End With

Set wkbk = Nothing
Set xlApp = Nothing

End Sub

The debug message is 'invalid procedure call or argument'

It just seems to hate .Add for some reason, but .AddColorScale is no problem

Any help would be really appreciated.

Does this answer your question? [Conditional Formatting using Excel VBA code](https://stackoverflow.com/questions/13661965/conditional-formatting-using-excel-vba-code) — June7, Oct 07 '20 at 17:33
I'm not trying to overlap two conditional formats onto the same range - I'm just trying to get conditional formatting for .range("R:R") above to work. The formatting for "P:P" and "Q:Q" already functions perfectly. (it still doesn't work if I delete all the P:P and Q:Q formatting, I'm just trying to show that .AddColorScale works, but .Add doesn't) — ChubbySkeleton, Oct 08 '20 at 08:55

Exporting access query to VBA, conditional formatting allows .AddColorScale, but not .Add

0 Answers0