Forum Discussion
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 us...
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
https://bettersolutions.com/excel.htm