Return lowest value in a range of cells that have been highlighted by conditional formatting?

al02847

New Member
Joined
Jan 24, 2023
Messages
10
Office Version
  1. 2019
I am trying to return the lowest value in a range of cells that have been highlighted by conditional formatting out of an even larger range of cells.

For example in a range of cells (E12:DA12), AD12 = 70 and AD13 = 75 and are both coloured red by conditional formatting. As 70 is lower than 75, in a different cell (D12) I want to return the value 70.

The conditional formatting used to highlight the cells red is =MEDIAN(E$2,E$5,$C10)-MEDIAN(E$2,E$5,$B10)>0

I am not sure if it's possible?

Thanks in advance for any advice.

Screenshot 2025-02-03 102504.png
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Try:
VBA Code:
Sub ReturnLowestValue()
    Application.ScreenUpdating = False
    Dim v As Variant, arr() As Variant, r As Long, c As Long, lRow As Long, lCol As Long
    lRow = Range("A" & Rows.Count).End(xlUp).Row
    lCol = Cells(10, Columns.Count).End(xlToLeft).Column
    ReDim arr(1 To lRow - 9)
    v = Range("E10:E" & lRow).Resize(, lCol - 4).Value
    For r = LBound(v) To UBound(v)
        For c = LBound(v, 2) To UBound(v, 2)
            If Cells(r + 9, c + 4).DisplayFormat.Interior.Color = 8028906 Then
                If Cells(r + 9, c + 5).DisplayFormat.Interior.Color = 8028906 Then
                    arr(r) = WorksheetFunction.Min(Range(Cells(r + 9, c + 4), Cells(r + 9, c + 5)))
                    Exit For
                Else
                    arr(r) = CLng(Cells(r + 9, c + 4))
                    Exit For
                End If
            End If
        Next c
    Next r
    Range("D10").Resize(lRow - 9) = Application.Transpose(arr)
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
Hello, thank you very much for writing this. How would this work when I need to pull it down all the cells in Column D? I feel maybe it is not possible.
 
Upvote 0
You are very welcome. : ) The code will automatically adjust to the number of Activities in column A.
 
Upvote 0
You are very welcome. : ) The code will automatically adjust to the number of Activities in column A.
Hello, this is working amazingly!!! So pleased, thank you.

However i am now trying to get this to run every time something changes in the worksheet. Google tells me I need to open the code with Private Sub worksheet_change(ByVal target As Range) but I can't get it to work with your code as I have two opening subs (?) and I get "Compile error: expected end sub" I don't know how to amend your code to make it work. Sorry probably really simple question but I'm utterly clueless about VBA. Thanks in advance for any further help!
 
Upvote 0
run every time something changes in the worksheet.
Do you want it to run any time something changes in any cell in the sheet or only when something changes in a certain column or range of cells?
 
Upvote 0

Forum statistics

Threads
1,226,266
Messages
6,189,936
Members
453,583
Latest member
Ok_category1816

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top