Forum Discussion

raneerina8102's avatar
raneerina8102
Copper Contributor
Jun 09, 2018

conditional formatting using macro

I am using conditional formatting to find the min. value in each row.
it is working correctly if i were to use the conditional formatting at the ribbon and keying in the formula but when i run it using macro, it take the 0 as the lowest value. 

All column are formatted to number value.
Any one can help?

My formula is 
=A2=MIN(IF($A2:$C2<>0,$A2:$C2))


Macros and VBA
  • Russell Proctor's avatar
    Russell Proctor
    Brass Contributor
    Jun 13, 2018

    Hi,

    The formula you are using is an Array Formula.

    These types of formulas are not supported when applying conditional formatting using VBA.

     

    There are two solutions:

    1) Change the formula to use a different worksheet function

    =A2=SMALL($A2:$C2,COUNTIF($A2:$C2,0)+1)

    This formula is not an array formula and will also give you the smallest number, excluding zeros.

     

    If you are using Excel 2016 you could use the MINIFS function which was introduced in an update to Office 2016. This formula will also give you the smallest number, excluding zeros.

    =A2=MINIFS($A2:$C2,$A2:$C2,"<>0")

     

    Hope this helps
    Russell

Resources