• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 365
  • Last Modified:

Problem with Conditional Formatting Macro

Folks,
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.
NonZero.xlsm
0
Frank Freese
Asked:
Frank Freese
  • 3
  • 3
  • 2
1 Solution
 
FlysterCommented:
This should take care of the zeros:
Sub LowestNonZero()
'
' LowestNonZero Macro
'

'
    Range("A2:D10").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
       "=A2=MIN(IF($A$2:$D$10<>0,$A$2:$D$10))"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    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

Flyster
0
 
barry houdiniCommented:
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:

=AND(A2<>0,COUNTIFS($A$2:$D$10,"<"&A2,$A$2:$D$10,"<>0")=0)

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.

"=AND(A2<>0,COUNTIFS($A$2:$D$10,""<""&A2,$A$2:$D$10,""<>0"")=0)"

regards, barry
0
 
Frank FreeseAuthor Commented:
barry,
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?
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
barry houdiniCommented:
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
0
 
FlysterCommented:
@ 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!

Paul
0
 
barry houdiniCommented:
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
0
 
Frank FreeseAuthor Commented:
Barry,
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.
0
 
Frank FreeseAuthor Commented:
thank you very much
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now