CODEWITHSUNDEEP
vbaexcelexcel-formula
jarabe787
jarabe787

Reputation: 21

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

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

Upvotes: 2

Views: 252

Answers (1)

Lowpar
Lowpar

Reputation: 907

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

formula comparison

Upvotes: 1

Related Questions