My Excel conditional formatting formula works manually but not coded in VBA

Question

I created this formula manually in "Conditional Formatting" graphic interface

=IF(AND($M7<>""SpecificDeparment"",NOT(ISBLANK($O7))),TRUE)

It should validate that for only one Certain Department accepts an ID, others must not have an id, filling the error ID cell with RED, It worked perfectly fine manually, I replaced the formulas to the RC Format

=IF(AND(RC[-2]<>""SpecificDeparment"",NOT(ISBLANK(RC))),TRUE)

It runs and makes no difference to Worksheet, but if I let only one condition (example: =IF($RC[-2]<>""SpecificDeparment"",TRUE) it works and changes my column Fill Color.

Here is the code I use for assigning the format in VBA:

With .Range(wrkbook.Sheets("Data").Cells(FirstRow, .Range("ID_Marker").Column), wrkbook.Sheets("Data").Cells(LastRow, .Range("ID_Marker").Column))

 .FormatConditions.Add Type:=xlExpression, Formula1:="=IF(AND(RC[-2]<>""SpecificDeparment"",NOT(ISBLANK(RC))),TRUE)"

    .FormatConditions(.FormatConditions.Count).SetFirstPriority
       With .FormatConditions(1)
        .Font.ColorIndex = MustFixDataFontColor
            .Font.Bold = MustFixDataFontBold
            .Interior.ColorIndex = 3
            .StopIfTrue = False
        End With

   End With
        DoEvents

I know the formula is good - is it really? R1C1 does not use dollars. — GSerg
When I put your formula in a msgbox there are missing inverted commas in the specificdepartment there is only one set of inverted commas, i.e., "specificdepartment " — Lowpar
SpecificDeparment appears to be a misspelling of SpecificDepartment. — halfer
The RC doesn't have $ in the formula I have in excel, I accidently put them while I was editing the code for this page. And the misspelling is no the error, I have the real name the Deparment on the code but replace it to not share the Corporate Information. — jarabe787
The inverted commas should be two pairs because if not the VBA code sends a Error Message, the ""DoubleInvertedCommas"" is the way to do it in code. any ideas of what is my problem? :( — jarabe787

Lowpar Lowpar · Accepted Answer · 2016-07-15T21:52:56

The formula on the top is from your excel formula, the formula below is the code produced when the vba formula is put into a msgbox

My Excel conditional formatting formula works manually but not coded in VBA

1 Answers