conditional formating to compare columns

[hitch_hiker]

I am trying to set up conditional formating to colour the cell if for example L10 < E10, but only if data exists in E10 for the entire column(s)

the perfect world would be if L10 < E10 only if G10 exists, but that might just be pushing a bit too much

 

[T. Valko]

I am trying to set up conditional formating to colour the cell if for example L10 < E10, but only if data exists in E10 for the entire column(s)

the perfect world would be if L10 < E10 only if G10 exists, but that might just be pushing a bit too much

Not real clear.

Can you post some sample data and tell us what result you expect?

What version of Excel are you using?

 

[hitch_hiker]

Thanks T. Valko,

sorry about the delay in responding, see below

column E | column G | column L
10 | 12.5 | 9.38
9.5 | 12.5 | 11
| |
29 | | 12
17.5 | 15 | 18
| |
6.5 | 38 | 4.8
13.5 | 7 | 12

this would be the prefered format


10 | 12.5 | 9.38
9.5 | 12.5 | 11
| |
29 | | 12
17.5 | 15 | 18
| |
6.5 | 38 | 4.8
13.5 | 7 | 12


this would be suitable

My appologies for not using html maker, but I'm not at my computer

 

[Vidar]

Hi

Select the range in column L. If the first cell the selection is in row 10, use this formula in Conditional Formatting
=AND(G10<>"",L10 less than E10)<e1)<e1)
<e1)<e10)


Replace "less than" with <
(parts of the formula disappeared after L10 when I used < in this post. Strange )

And choose the format of the cell when the conditions is TRUE

Vidar</e1)<e10)
</e1)<e1)
<e10)<e1)<e1)
<e10)<e1)<e1)
<e10) <="" strong=""></e10)><e10) <="" strong=""><e1)<e1)
</e1)<e1)
</e10)></e10)<e1)<e1)
</e10)<e1)<e1)

 

[hitch_hiker]

Thanks Vidar,
I will have to try it tomorrow, i left my laptop at work, I really have only done system conditions up till now, the range in column L will be L:L so obviously I make the formula L1 etc

 

[T. Valko]

Hi

Select the range in column L. If the first cell the selection is in row 10, use this formula in Conditional Formatting
=AND(G10<>"",L10 less than E10)<E1)<E1)
<E1)<E10)


Replace "less than" with <
(parts of the formula disappeared after L10 when I used < in this post. Strange )

And choose the format of the cell when the conditions is TRUE

Vidar</E1)<E10)

</E1)<E1)
<E10)<E1)<E1)
<E10)<E1)<E1)
<E10) strong="" <=""></E10)><E10) strong="" <=""><E1)<E1)
</E1)<E1)
</E10)></E10)<E1)<E1)
</E10)<E1)<E1)[ QUOTE]

If L10 can be an empty cell then you might want to add another test:

=AND(G10<>"",L10<>"",L10 < E10)

 

[hitch_hiker]

thanks Biff,
there certainly will be empty cells in L , so thanks for the heads up, didn't think about that

 

[T. Valko]

thanks Biff,
there certainly will be empty cells in L , so thanks for the heads up, didn't think about that

You're welcome. Thanks for the feedback!