Excel conditional formatting if two condition satisfies

Question

I have excel sheet, which has value stored as price in "D" column

now, I want to highlight particular cell in column "D" which satisfies below 2 conditions

XFB9<=0.02
which cell has highest value among cells that has fulfilled above condition (column "D")

issue I find here in writing formula is, to define range Because, how do I define range to get highest value from that cell which fulfills no. 1 condition?, because it varies time to time

I have written following formula in conditional formatting for no. 1 condition

=XFB9<=0.02

Kindly help with your solution on this issue, any help highly appriciated.

Thank you so much for putting efforts and giving time, but I prefer conditional formating – Aman Patel 22 mins ago Tags edited to reflect this. — Siddharth Rout

Simon Simon · Accepted Answer · 2020-09-19T09:21:04

It may not necessarily be the best way to do this but it's a way I found. Instead of using conditional formatting this vba can do both.

Dim rng As Range, cell As Range, previouscell As Variant

Set rng = Sheet1.Range("F22:F61") 'set your range

For Each cell In rng
    If cell.Value <= 0.02 Then
        cell.Interior.Color = vbYellow 'color for the values <=0.02
        If cell.Value > previouscell Then
            previouscell = cell.Value
        End If
    End If
Next

For Each cell In rng
    If cell.Interior.ColorIndex > 0 Then
        If cell.Value = previouscell Then
            cell.Interior.Color = vbRed 'color for max value
        End If
    End If
Next

Ok I found a Conditional Formatting solution. Add a second condition with the formula:

=A1=MAX(IF($A$1:$A$20<=0.02,$A$1:$A$20))

Obviously you need to adjust the range. The first A1 being the first cell in the range and then the fixed range for the other 2 sections.

This highlights the max value that also is <=0.02.

Excel conditional formatting if two condition satisfies

2 Answers