Conditional formatting

Cime14

New Member
Joined
Dec 19, 2017
Messages
28
Hi!

I kindly ask for a little help. Any idea how to highlight values with conditional formatting?
For example: I would like highlight value within "Cycle times", if the total is bigger than value in "VMD payment terms".

For row 1 I should get highlighted "Cycle time 1" (116>30), for row 2 "Cycle time 4" (13+0+1+7=21 > 15) etc.

A B C D E F
Cycle time 1 Cycle time 2 Cycle time 3 Cycle time 4 Total cycle time VMD payment terms
11630212130
130172115
402524930

<colgroup><col span="4"><col><col></colgroup><tbody>
</tbody>
<strike></strike>


Many thanks!!

<colgroup><col span="4"><col><col></colgroup><tbody>
</tbody>
<strike></strike>
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Hi!

I kindly ask for a little help. Any idea how to highlight values with conditional formatting?
For example: I would like highlight value within "Cycle times", if the total is bigger than value in "VMD payment terms".

For row 1 I should get highlighted "Cycle time 1" (116>30), for row 2 "Cycle time 4" (13+0+1+7=21 > 15) etc.

A B C D E F
Cycle time 1 Cycle time 2 Cycle time 3 Cycle time 4 Total cycle time VMD payment terms
11630212130
130172115
402524930

<tbody>
</tbody>
<strike></strike>
Many thanks!!

<tbody>
</tbody>
<strike></strike>




In Conditional Formatting

Use new rule
Use formula to determine which cells to format
enter this formula =SUM($A2:$D2)>$F2
and in applies to enter $F2

Regards,

Dan.
 
Last edited:
Upvote 0
Dear both!

Thanks for your help but with this forlula I get colured entire row.
I want to be colured just one particular field (field where value extends compared one).

I want to compare values of cycle time with VMD payment terms.

For example: - in first row value 31 ("Cycle time 1" field) should be marked (31 > 30)
- in second row value 3 ("Cycle time 2" field) should be marked (28+3=32>30)


Cycle time 1 Cycle time 2 Cycle time 3 Cycle time 4 Total cycle time VMD payment terms
31 3 2 9 43 30
28 3 2 8 41 30


Thanks in advance!
 
Upvote 0
Sorry,

Just to clarify, do you only want to highlight the cell value which causes the cumulative total to exceed the VMD? what do you want to do with further cells which increase the value should these also be marked?

Regards,

Dan.
 
Upvote 0
Hi Dan!

yes, you are correct. I want to highlight the cell value which causes the cumulative total. The further cell should remain unmarked.

Thanks, regards
 
Upvote 0
Hi Dan!

yes, you are correct. I want to highlight the cell value which causes the cumulative total. The further cell should remain unmarked.

Thanks, regards

I Cannot think of an easy way to accomplish this with conditional formatting rules. The only way would be to start by highlighting everything where the total exceeds, and then keep testing back the sums and removing the highlight until you reach the point where it no longer exceeds.

This would be quite messy, and possibly prone to mistakes over time.

This is probably better placed to be a VBA script that can be run on demand.

Possibly someone may know a way of handling this better in conditional formatting.
If you are interested in a VBA solution, let me know and i will have a look into it.

Thanks,

Dan.
 
Upvote 0
Hi Dan,

sure! I am interested in a VBA solution however I am not familiar with it :/
I kindly ask you to explain a little how to use mentioned solution.

Thanks in advance!

Regards
 
Upvote 0
how about this formula on conditional formatting..
B4:E4>$G4

assuming that you have cells like the following
cycletime1(B3) cycletime2(C3) cycletime3(C3) cycletime4(C3) totaltime(F3) VMD payment terms(G3)
116 (B4) 3(C4) 0(D4) 2(E4) 121(F4) 30(G4)
13(B5) 0(C5) 1(D5) 7(E5) 21(F5) 15(G5)
40(B6) 2(C6) 5(D6) 2(E6) 49(F6) 30(G6)


conditional formatting range is B4:E6
and make sure the active cell is on B4
 
Last edited:
Upvote 0
Hi yosegmail,

thank you for your reply. This formula works but does not highlight cell value which causes the cumulative total > measured value.

I would like to have a formula which would highlight cell E5 (7) because cumulative value of B5 + C5 + D5 + E5 > G5. Since value gets > G5 in field E5 this field should be highlighted.

Thanks, regards
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

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