Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Problem with Conditional Formatting Macro

Posted on 2013-11-28
Medium Priority
Last Modified: 2013-11-29
When you open the attached workbook and on the first tab click display data I'm trying to display the lowest value in the table excluding zero. I call a macro that executes the data to be displayed with Conditional Formatting. I cannot find anything wrong with the code, yet it highlights zero values which is incorrect. In the table the lowest nonzero value is 2, not zero.
Question by:Frank Freese
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
LVL 22

Expert Comment

ID: 39684614
This should take care of the zeros:
Sub LowestNonZero()
' LowestNonZero Macro

    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
    With Selection.FormatConditions(1).Font
        .Bold = True
        .Italic = False
        .Color = -16711681
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorLight2
        .TintAndShade = -0.249946592608417
    End With
    Selection.FormatConditions(1).StopIfTrue = False
End Sub

Open in new window

LVL 50

Accepted Solution

barry houdini earned 2000 total points
ID: 39685154
HelloFlyster - isn't that the same formula that's already there?

I think this is caused by a bug in conditional formatting in Excel 2007 - array formulas used in conditional formatting need to be "refreshed" before they work. When I opened the file I saw the zeroes formatted, if I go into conditional formatting > Edit > without changing the formula but hitting OK it then works and formats 2 as required.

If you can use a non-array formula for conditional formatting that will work without fail. For that you can use this formula:


that will highlight the lowest value if it's not zero. Just put that in quotes in your macro in place of the current formula, with quotes doubled-up, i.e.


regards, barry

Author Comment

by:Frank Freese
ID: 39685548
You correct in that Flyster's fromula was the same as I had submitted.
Your first suggestion was rejected with a Compiled error: Expression expected.
However, the second alternative worked.
What is confusing is when I use the code I provided in a macro it does not give the expected results. However, if I do not use a macro, it worked just fine.
Any thoughts on that before I award you points?
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 50

Expert Comment

by:barry houdini
ID: 39685620
Do you mean if you set up the conditional formatting manually (with the original formula) it works OK?

When I have seen this problem in the past the conditional formatting works OK when initially applied ......but then it either does nothing or gives wrong results when the workbook is closed and re-opened - the CF formula remains there and if you go in to CF as if to edit, but leave the formula the same, it then works OK again.

I tried searching for more details but I can't find a good link

regards, barry
LVL 22

Expert Comment

ID: 39685648
@ Barry,

You're right. I don't know what happened but it is the same. The one I ran prior to submitting was highlighting the 2 in B5. I'll have to check it when I get back to the office. My apologies to fh_freese!

LVL 50

Expert Comment

by:barry houdini
ID: 39685709
There's nothing intrinsicly wrong with that formula, Paul. It's an excel bug that stops it working as it should - I don't think that bug exists outside 2007 so if you tested in another version it may have worked perfectly.

regards, barry

Author Comment

by:Frank Freese
ID: 39685710
When I set up the Conditional Format manually no problem. When I duplicated the same thing to create the macro I got the wrong results.
Paul, I do appreciate you work, however, I'll need to accept the solution Barry offered.
I do appreciate everyone's help.

Author Closing Comment

by:Frank Freese
ID: 39685713
thank you very much

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question