0 votes
in VBA by Beginner (5 points)

Task is to highlight all the rows which have minimum quoted rate for a particular item:

For eg: for item 101, Rs. 2000 is the minimum quote so highlight all the rows which contains quoted rate of 2000 for item 101.

Same is the case for Item 102.

 

For getting it done I have recorded this piece of code

Sub Conditional_Format()

    Range("A2:H10").Select


    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=$G2=MIN(IF($A$2:$A$10=$A2,$G$2:$G$10,10000000000))"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
    End With

    Selection.FormatConditions(1).StopIfTrue = False

End Sub

At the end of the code I see that only the second row is getting highlighted but the eighth row, which contains the lowest quoted rate for item 102, is not getting highlighted.

Can someone help??

 

by Super Expert (2.9k points)

A couple clarifications will help us develop a simplified solution for you.

  1. Will the data always already be sorted by the RS column? 
  2. Will the Rank column already be populated when you want to apply the conditional formatting?
by Beginner (5 points)
+1
Yes for both questions.

1 Answer

0 votes
by Super Expert (2.9k points)
selected by
 
Best answer

This macro will check for the condition where the value in column H equals "L1," which represents the lowest value per item number. This is a simpler formula than trying to dynamically find the minimum across subgroups. The macro then applies yellow conditional formatting across the row:

Sub Conditional_Format()
Dim lastrow As Long
Dim rng As Range
lastrow = Range("A" & Rows.Count).End(xlUp).Row
Set rng = Range("A2:H" & lastrow) 'set the full data range to a variable
With rng
    .FormatConditions.Delete       'delete all existing conditional formatting
    .FormatConditions.Add Type:=xlExpression, Formula1:="=$H2=""L1""" 'add new conditional formatting
    .FormatConditions(rng.FormatConditions.Count).SetFirstPriority
    .FormatConditions(1).Interior.Color = 65535
    .FormatConditions(1).StopIfTrue = False
End With
End Sub

Here's how the final output looks:

VBA Conditional Formatting

by Beginner (5 points)
I agree with you but why the CF i have written is not working.

When i simply put the formula in CF without any VBA it is working fine.

One more thinng i will bring to ur notice:

What I did is i added few more lines of code that will save and close WB. To my surprise when i open the workbook CF is applied in 2nd rwo & 8th Row as you have shown in above image. CF is getting applied as desired.

Kinldy commnet what VBA is doing and how all this is happening. Eager to listen from u!!!

Thnaks for the help!!!
by Super Expert (2.9k points)

This appears to be a similar problem to what a user discovered when writing a macro to conditionally highlight the intersecting rows/columns. It seems like it's a bug in Excel's rendering of conditional formatting post-VBA.

by Beginner (5 points)
Thanks for your time and explanation.

Atleast i came to some conclusion. Now my restlesness has resided after knowing that this is a bug.

Will come back to u in future too as ur VBA codes are very simple and easy to understand.

Please maintain this througout your career as it help a lot of people like me.

The message that you want to convey thro' ur blogs are being propoerly conveyed.

All the best!!!

Welcome to wellsr Q&A
wellsr Q&A is the VBA and Python programming community that rewards you for learning how to code.

Getting Started
Register
VBA Cheat Sheets (On Sale Now)

Earn free prizes for asking VBA and Python questions and for answering questions asked by others in our community.

Looking for something else? Hire our professional VBA Help, instead.

What makes us different?
Our points system rewards you with a chance for free gifts based on the quality of your questions and answers. All you have to do is post and you could get rewarded, like these members:

ParserMonster $25 Amazon Gift Card
Hightree $10 Amazon Gift Card
Thales1 $10 Amazon Gift Card
runfunke $10 Amazon Gift Card
coolag $10 Amazon Gift Card
Siew Hun $10 Amazon Gift Card

So, why don't you join us? It really is an encouraging way to motivate members in our VBA and Python community.

Register

For more programming tips visit the VBA Tutorials Blog and the Python Tutorials Blog.

...