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 (3.2k 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 (3.2k 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 (3.2k 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
Ask any questions you have about VBA and Python and our community will help answer them. wellsr Q&A is the standalone question and answer platform for wellsr.com. If you have a question about one of our specific tutorials, please include a link back to the tutorial.

Getting Started
Register
VBA Cheat Sheets (On Sale Now)

Looking for something else? Hire our team directly through ourVBA Help page, instead.

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

...