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

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
It is hard to work with a picture. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach the desired selected range (not a picture) of your data. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).
 
Upvote 0
Hello, thank you. I have tried to use XL2BB here (fingers crossed it works):

SampleSheet.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJ
7ActivityStart Date TimeEnd Date TimeThresholdWorking Hours (core working hours highlighted in green)
8Mon 17/03Tue 18/03Wed 19/03Thu 20/03Fri 21/03Sat 22/03Sun 23/03
907:00-08:0008:00-18:0018:00-19:0019:00-22:0022:00-07:0007:00-08:0008:00-18:0018:00-19:0019:00-22:0022:00-07:0007:00-08:0008:00-18:0018:00-19:0019:00-22:0022:00-07:0007:00-08:0008:00-18:0018:00-19:0019:00-22:0022:00-07:0007:00-08:0008:00-18:0018:00-19:0019:00-22:0022:00-07:0007:00-08:0008:00-13:0013:00-14:0014:00-22:0022:00-07:0007:00-21:0021:00-07:00
10Task 1Sat 22/03/2025 00:00Sat 22/03/2025 02:00?7075706555707570655570757065557075706555707570655570757065556555
11Task 2Sat 22/03/2025 02:00Sat 22/03/2025 07:00557075706555707570655570757065557075706555707570655570757065556555
12Task 3Sat 22/03/2025 07:00Sat 22/03/2025 08:30707075706555707570655570757065557075706555707570655570757065556555
13Task 4Sat 22/03/2025 08:30Sat 22/03/2025 12:00757075706555707570655570757065557075706555707570655570757065556555
14Task 5Sat 22/03/2025 12:00Sat 22/03/2025 13:30707075706555707570655570757065557075706555707570655570757065556555
15Task 6Sat 22/03/2025 13:30Sat 22/03/2025 17:00657075706555707570655570757065557075706555707570655570757065556555
16Task 7Sat 22/03/2025 17:00Sun 23/03/2025 00:00557075706555707570655570757065557075706555707570655570757065556555
17Task 8Sun 23/03/2025 00:00Sun 23/03/2025 01:30557075706555707570655570757065557075706555707570655570757065556555
18Task 9Sun 23/03/2025 01:30Sun 23/03/2025 04:00557075706555707570655570757065557075706555707570655570757065556555
19Task 10Sun 23/03/2025 04:00Sun 23/03/2025 06:00557075706555707570655570757065557075706555707570655570757065556555
20Task 11Sun 23/03/2025 06:00Sun 23/03/2025 09:00557075706555707570655570757065557075706555707570655570757065556555
21Task 12Sun 23/03/2025 09:00Sun 23/03/2025 11:007075706555707570655570757065557075706555707570655570757065556555
22Task 13Sun 23/03/2025 11:00Sun 23/03/2025 12:007075706555707570655570757065557075706555707570655570757065556555
23Task 14Sun 23/03/2025 12:00Sun 23/03/2025 13:007075706555707570655570757065557075706555707570655570757065556555
24Task 15Sun 23/03/2025 13:00Sun 23/03/2025 18:007075706555707570655570757065557075706555707570655570757065556555
25Task 16Sun 23/03/2025 18:00Sun 23/03/2025 22:007075706555707570655570757065557075706555707570655570757065556555
26Task 17Sun 23/03/2025 20:00Sun 23/03/2025 23:007075706555707570655570757065557075706555707570655570757065556555
27Task 18Sun 23/03/2025 21:00Mon 24/03/2025 00:007075706555707570655570757065557075706555707570655570757065556555
28Task 19Mon 24/03/2025 00:00Mon 24/03/2025 06:007075706555707570655570757065557075706555707570655570757065556555
Noisy Activities
Cell Formulas
RangeFormula
E8E8=IF(TEXT(B10,"ddd")="sun",DATE(YEAR(B10),MONTH(B10),DAY(B10))-6,IF(TEXT(B10,"ddd")="sat",DATE(YEAR(B10),MONTH(B10),DAY(B10))-5,IF(TEXT(B10,"ddd")="fri",DATE(YEAR(B10),MONTH(B10),DAY(B10))-4,IF(TEXT(B10,"ddd")="thu",DATE(YEAR(B10),MONTH(B10),DAY(B10))-3,IF(TEXT(B10,"ddd")="wed",DATE(YEAR(B10),MONTH(B10),DAY(B10))-2,IF(TEXT(B10,"ddd")="tue",DATE(YEAR(B10),MONTH(B10),DAY(B10))-1,DATE(YEAR(B10),MONTH(B10),DAY(B10))))))))
J8,O8,T8,Y8,AD8,AI8J8=E8+1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A10:A28Expression=MOD(ROW(),2)=0textNO
E10:CV72Expression=MEDIAN(E$2,E$5,$C10)-MEDIAN(E$2,E$5,$B10)>0textNO
A29:A72,B10:CV72Expression=MOD(ROW(),2)=0textNO
 
Upvote 0
I was able to copy the data but none of the formulas including the one for conditional formatting are included. When you use XL2BB, make sure that you check the boxes for cell formulas and conditional formatting in the "Additional Info" section of the menu before you click "Mimi Sheet". Please try that and post the selected data. The other option is to upload your file as I suggested in Post #2.
 
Upvote 0
I was able to copy the data but none of the formulas including the one for conditional formatting are included.
The CF formulas are never copied, but the ones in row 8 should have copied, if you used the copy icon.
 
Upvote 0
@al02847 what have you got in rows 2 & 4 as that is what the CF rule is looking at.

Also you can simplify the formula in E8 like
Excel Formula:
=WORKDAY.INTL(B10-1,-1,"0111111")
 
Upvote 0
@Fluff
I used the copy icon but no formulas were present. I applied the conditional formatting formula to E10:CV72 but all the cells in the range turned red.
 
Upvote 0
The formulas in row 8 are there for me when I copied it. Did you click or Alt Click?
Also the CF won't work without knowing what values are in rows 2 & 5
 
Upvote 0
Hello, thanks for your replies so far. I have tried again here:

SampleSheet.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJ
1################################################################################################################################################################################################
2################################################################################################################################################################################################
307:0008:0018:0019:0022:0007:0008:0018:0019:0022:0007:0008:0018:0019:0022:0007:0008:0018:0019:0022:0007:0008:0018:0019:0022:0007:0008:0013:0014:0022:0007:0021:00
4################################################################################################################################################################################################
5################################################################################################################################################################################################
608:0018:0019:0022:0007:0008:0018:0019:0022:0007:0008:0018:0019:0022:0007:0008:0018:0019:0022:0007:0008:0018:0019:0022:0007:0008:0013:0014:0022:0007:0021:0007:00
7ActivityStart Date TimeEnd Date TimeThresholdWorking Hours (core working hours highlighted in green)
8Mon 17/03Tue 18/03Wed 19/03Thu 20/03Fri 21/03Sat 22/03Sun 23/03
907:00-08:0008:00-18:0018:00-19:0019:00-22:0022:00-07:0007:00-08:0008:00-18:0018:00-19:0019:00-22:0022:00-07:0007:00-08:0008:00-18:0018:00-19:0019:00-22:0022:00-07:0007:00-08:0008:00-18:0018:00-19:0019:00-22:0022:00-07:0007:00-08:0008:00-18:0018:00-19:0019:00-22:0022:00-07:0007:00-08:0008:00-13:0013:00-14:0014:00-22:0022:00-07:0007:00-21:0021:00-07:00
10Task 1Sat 22/03/2025 00:00Sat 22/03/2025 02:00?7075706555707570655570757065557075706555707570655570757065556555
11Task 2Sat 22/03/2025 02:00Sat 22/03/2025 07:00557075706555707570655570757065557075706555707570655570757065556555
12Task 3Sat 22/03/2025 07:00Sat 22/03/2025 08:30707075706555707570655570757065557075706555707570655570757065556555
13Task 4Sat 22/03/2025 08:30Sat 22/03/2025 12:00757075706555707570655570757065557075706555707570655570757065556555
14Task 5Sat 22/03/2025 12:00Sat 22/03/2025 13:30707075706555707570655570757065557075706555707570655570757065556555
15Task 6Sat 22/03/2025 13:30Sat 22/03/2025 17:00657075706555707570655570757065557075706555707570655570757065556555
16Task 7Sat 22/03/2025 17:00Sun 23/03/2025 00:00557075706555707570655570757065557075706555707570655570757065556555
17Task 8Sun 23/03/2025 00:00Sun 23/03/2025 01:30557075706555707570655570757065557075706555707570655570757065556555
18Task 9Sun 23/03/2025 01:30Sun 23/03/2025 04:00557075706555707570655570757065557075706555707570655570757065556555
19Task 10Sun 23/03/2025 04:00Sun 23/03/2025 06:00557075706555707570655570757065557075706555707570655570757065556555
20Task 11Sun 23/03/2025 06:00Sun 23/03/2025 09:00557075706555707570655570757065557075706555707570655570757065556555
21Task 12Sun 23/03/2025 09:00Sun 23/03/2025 11:007075706555707570655570757065557075706555707570655570757065556555
22Task 13Sun 23/03/2025 11:00Sun 23/03/2025 12:007075706555707570655570757065557075706555707570655570757065556555
23Task 14Sun 23/03/2025 12:00Sun 23/03/2025 13:007075706555707570655570757065557075706555707570655570757065556555
24Task 15Sun 23/03/2025 13:00Sun 23/03/2025 18:007075706555707570655570757065557075706555707570655570757065556555
25Task 16Sun 23/03/2025 18:00Sun 23/03/2025 22:007075706555707570655570757065557075706555707570655570757065556555
26Task 17Sun 23/03/2025 20:00Sun 23/03/2025 23:007075706555707570655570757065557075706555707570655570757065556555
27Task 18Sun 23/03/2025 21:00Mon 24/03/2025 00:007075706555707570655570757065557075706555707570655570757065556555
28Task 19Mon 24/03/2025 00:00Mon 24/03/2025 06:007075706555707570655570757065557075706555707570655570757065556555
297075706555707570655570757065557075706555707570655570757065556555
307075706555707570655570757065557075706555707570655570757065556555
317075706555707570655570757065557075706555707570655570757065556555
327075706555707570655570757065557075706555707570655570757065556555
337075706555707570655570757065557075706555707570655570757065556555
347075706555707570655570757065557075706555707570655570757065556555
Noisy Activities
Cell Formulas
RangeFormula
E1:I1,E4:H4E1=$E$8
J1:N1,J4:M4J1=$J$8
O1:S1,N4:R4O1=$O$8
T1:X1,S4:W4T1=$T$8
Y1:AC1,X4:AB4Y1=$Y$8
AD1:AH1,AC4:AG4AD1=$AD$8
AI1:AJ1,AH4:AI4AI1=$AI$8
E2:AJ2,E5:AJ5E2=E1+E3
I4I4=J8
AJ4AJ4=$AK$8
E8E8=IF(TEXT(B10,"ddd")="sun",DATE(YEAR(B10),MONTH(B10),DAY(B10))-6,IF(TEXT(B10,"ddd")="sat",DATE(YEAR(B10),MONTH(B10),DAY(B10))-5,IF(TEXT(B10,"ddd")="fri",DATE(YEAR(B10),MONTH(B10),DAY(B10))-4,IF(TEXT(B10,"ddd")="thu",DATE(YEAR(B10),MONTH(B10),DAY(B10))-3,IF(TEXT(B10,"ddd")="wed",DATE(YEAR(B10),MONTH(B10),DAY(B10))-2,IF(TEXT(B10,"ddd")="tue",DATE(YEAR(B10),MONTH(B10),DAY(B10))-1,DATE(YEAR(B10),MONTH(B10),DAY(B10))))))))
J8,O8,T8,Y8,AD8,AI8J8=E8+1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A10:A28Expression=MOD(ROW(),2)=0textNO
E10:CV72Expression=MEDIAN(E$2,E$5,$C10)-MEDIAN(E$2,E$5,$B10)>0textNO
A29:A72,B10:CV72Expression=MOD(ROW(),2)=0textNO
 
Upvote 0

Forum statistics

Threads
1,226,264
Messages
6,189,928
Members
453,582
Latest member
Browny2821

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