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

al02847

New Member
Joined
Jan 24, 2023
Messages
13
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
 
Delete the module where the macro currently exists. Then copy and paste the macro below into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. The macro will run automatically whenever you make a change in any cell in the sheet.
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Application.ScreenUpdating = False
    Application.EnableEvents = 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.EnableEvents = True
    Application.ScreenUpdating = True
End Sub
 
Upvote 0

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Hello,

thank you so so so so much. This is working sooooooo almost perfectly but I am losing my header for the row the macro is working on.

my actual spreadsheet has a slightly different layout to my 'sample sheet' so I feel it's maybe something to do with how I am amending the code to work on the right cells.

E.g. Column D is now column G, column E is now J, and I have also added some rows above so the rows have shifted all down by 3. I feel like this is my problem - when I adjust D10 to G13 it doesn't work properly :(

1738768954892.png

Hiya, thanks so mcuh I had this working perfectly in my sample sheet and now i've tried to move it to my actual spreadsheet I can't get it to work. I know it's because my rows / columns have changed but I am struggling to work out where the code needs updating. Sorry I couldn't use xl2bb as it said the area I wanted to copy was too large. Here is a screenshot instead:

1738847219077.png
 
Upvote 0
Ah I have worked out the problem. i think I managed to sort out the new rows/columns but the sheet is normally protected which is why it won't work. so difficult !!!
 
Upvote 0
Glad it worked out. If the sheet is protected, the macro can unprotect it, run the code and then protect it again when done using something like this:
VBA Code:
ActiveSheet.Unprotect
'your code here
ActiveSheet.Protect
 
Upvote 0
Thanks heaps for your help!
 
Upvote 0

Forum statistics

Threads
1,226,452
Messages
6,191,130
Members
453,641
Latest member
enfkkviesm

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