Conditional formatting

[Cime14]

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
[TABLE="width: 610"]
<colgroup><col span="4"><col><col></colgroup><tbody>[TR]
[TD] Cycle time 1[/TD]
[TD] Cycle time 2[/TD]
[TD] Cycle time 3[/TD]
[TD] Cycle time 4[/TD]
[TD] Total cycle time[/TD]
[TD] VMD payment terms[/TD]
[/TR]
[TR]
[TD="align: center"]116[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]121[/TD]
[TD="align: center"]30[/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]21[/TD]
[TD="align: center"]15[/TD]
[/TR]
[TR]
[TD="align: center"]40[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]49[/TD]
[TD="align: center"]30[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>[TABLE="width: 610"]
<colgroup><col span="4"><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

[/TD]
[/TR]
[TR]
[TD]Many thanks!![/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>

 

[Youngdand]

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
[TABLE="width: 610"]
<tbody>[TR]
[TD] Cycle time 1[/TD]
[TD] Cycle time 2[/TD]
[TD] Cycle time 3[/TD]
[TD] Cycle time 4[/TD]
[TD] Total cycle time[/TD]
[TD] VMD payment terms[/TD]
[/TR]
[TR]
[TD="align: center"]116[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]121[/TD]
[TD="align: center"]30[/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]21[/TD]
[TD="align: center"]15[/TD]
[/TR]
[TR]
[TD="align: center"]40[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]49[/TD]
[TD="align: center"]30[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>[TABLE="width: 610"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Many thanks!![/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
<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.

 

[Sumitpal SIngh Sandhu]

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 (*** applies to $2:$2) this will color the entire row

Regards,

Sumit Sandhu

 

[Cime14]

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!

 

[Youngdand]

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.

 

[Cime14]

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

 

[Youngdand]

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.

 

[Cime14]

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

 

[yosegmail]

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

 

[Cime14]

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