Data Validation to Highlight Cells

[HangMan]

I have a number of cells in each row of a sheet where the user will enter percentages. I need to find a way to highlight all the cells in that row (columns A, C, E, G, I) if the total of all the cells <> 100%. Ideally I only want the cells highlighted after the last entry has been made, so if the user enters 20% in A2, 20% in C2, 20% in E2, 20% in G2, and 10% in I2, then all these cells should be highlighted to show that the total of these cells doesn't = 100%. I don't want the data validation to kick in until all the cells have entries. The user will be asked to enter 0% if there is no percentage, so will be asked to complete all cells.

This needs to work for each row in the sheet.

Many thanks

[ramananhrm]

Please try this logic.

[Fotis1991]

Hi

Does something like this could works for you?

In CF rules.

Please Login or Register  to view this content.

[HangMan]

Hi Fotis1991,

Thanks for that, I like the logic. The only problem, which is down to me not explaining it very well, is that I2 could actuall = 0%, so while the logic works, it possibly needs to be something along the lines of 'Is Not Blank and >= 0', so meeting two conditions. I've tried playing around with it but can't get it to work!

[JosephP]

perhaps
=AND($I2>0,$I2<>"",SUM($A2,$C2,$E2,$G2,$I2)<>100)

[HangMan]

Hi JosephP,

That's what I thought, I tried (with my actual cell references)

=AND($AM5<>"",$AM5>=0,SUM($M5,$O5,$Q5,$S5,$U5,$W5,$Z5,$AB5,$AD5,$AF5,$AH5,$AJ5,$AL5,$AM5)<>100)

and

=AND(NOT(ISBLANK($AM5)),$AM5>=0,SUM($M5,$O5,$Q5,$S5,$U5,$W5,$Z5,$AB5,$AD5,$AF5,$AH5,$AJ5,$AL5,$AM5)<>100)

but neither work and I can't see why?

[Fotis1991]

This one looks to work. Does it?

=AND(OR($I2=0,SUM($A2,$C2,$E2,$G2,$I2)<>100),OR(COUNT($A2,$C2,$E2,$G2,$I2)=5))

[JosephP]

did the formula work before you added the new condition? if it did I reckon your cell isn't blank

[HangMan]

Hi Fotis1991,

Everything is fine unless I2 actually does = 0, then for some reason it falls over. I tried >= for I2 because the value in I2 could be either, but when I2 does = 0 it doesn't work? So if A2=20, C2=20, E2=30, G2=30 and I2=0, that's when it doesn't work and I still can't see why! I2 can = 0.

I tried

=AND(OR($I2>=0,SUM($A2,$C2,$E2,$G2,$I2)<>100),OR(COUNT($A2,$C2,$E2,$G2,$I2)=5))

but same problem!

[Fotis1991]

Here is an example of what i suggested.

[Fotis1991]

Hi Fotis1991,

So if A2=20, C2=20, E2=30, G2=30 and I2=0, that's when it doesn't work and I still can't see why! I2 can = 0.

If you add these you get 100! So as one of the conditions is <>100, then correctly does not highlighted anything. Isn't this correct?

[HangMan]

Hi Fotis,

The problem is that even when they do = 100 and I2 = 0, the cells 'are' still highlighting! That's the bit I can't figure out...

[Fotis1991]

An option is to use another rule as first, using the white color...

=SUM($A2,$C2,$E2,$G2,$I2)=100

This will keep all the rows that have 100 as result, whites...

[HangMan]

In thoery I think this should work:

=AND(SUM($A2,$C2,$E2,$G2,$I2)<>100,COUNT($A2,$C2,$E2,$G2,$I2)=5)

It seems to work on your sample, I just can't get it to work (yet) on my actual sheet, so I must be doing something wrong!

[JosephP]

are you sure you have the correct row active when applying the formula?

[newdoverman]

Try this.

Please Login or Register  to view this content.

All cells have to be filled and it doesn't matter in what order they are filled.

In the applies to field if you want only the cells in columns A, C, E, G and I coloured enter:

Please Login or Register  to view this content.

In the applies to field if you want the whole row A2:I2 coloured enter this

Please Login or Register  to view this content.

[HangMan]

Okay, so finally figured out the problem, I'm working with percentages so it should read <> 1 and not <> 100!

=AND(SUM($A2,$C2,$E2,$G2,$I2)<>1,COUNT($A2,$C2,$E2,$G2,$I2)=5)

Many thanks for everyone's input, right answers, wrong implementation by me!

[Fotis1991]

You are welcome and thanks for the feed back.

Make sure that you will mark this thread as SOLVED. Thank you.