Change color of a cell

[bdm1]

I have not done much vba in excel and any help would be appreciated. When a client has fallen below a determined benchmark for any 3 consecutive reporting reporting period, I would like to have the 3rd cell change to the color red. Can this be done? The info I have read thus far gives me instructions and syntax for a cell to change color if a total of a range falls below an assigned value, but not for 3 consecutive ranges. Any help on this would be most apprecuiated. Thanks

 

[ETID]

Have you tried a formula in a conditional format?


something like...



=if(and(a2<my_target,b2<my_target,c2<my_target),true,false)

wher my_target is any variable integer.

 

[bdm1]

Hi EDIT, thanks for your response. I have not tried anything yet because I was not sure of syntax or procedure. Can you elaborate a bit on your suggestion? And please be patient with me. I am very new at this excel thingy.....

 

[ETID]

Lets say you have values in column D,E,& F
in your case F would be the 3rd consecutive column

highlite column F,

....you then goto the format menu and choose conditional formatting.


change the cell value drop down to "formula is" and in the box to the right, type

=if(and(d1<10,E1<10,f1<10),true,false)

then select the formatting from the "format..." button
that you want to show when all 3 conditions are true.

click OK

 

[ETID]

Ooops substitute 10 in the formula, for a value the is meaningful to your situation.

 

[bdm1]

Hi EDIT, yes I understand the concept and procedure. Can this be done globally? To apply to the worksheet? I will be entering data for about 20 managers. Anytime that one or more has not reached benchmark for three consecutive reporting periods, the third cell should change to red. Using your example: values in column D,E,F,G,H. Value in D is ok., E,F are not and neither is G so need G to turn red. If H is also below benchmark, H turns to red. Cell continue to show red until benchmark is met. The cycle starts over. There really is not a total column and that is my problem.

 

[SkipVought]

Select the COLUMNS you want and then do the Conditional Formatting for ROW 1 using the SAME kind of relative/absolute cell referencing you would use for any formulas.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[bdm1]

Hi Skip, not being that familiar with excel vb, I need more elaboration. Are you saying that I can code the sheet so a cell will flag red if it is below the assigned value as long as the 2 previous contiguous values are also below par? Simple Example: the range of cells is A1 through A12. values run as follows: Cell A1 is ok. A2 is not. A3 is not. A4 is Not therefore turns red. A5 is not and cell remains red. A6 is ok and reverts to white until it again is under par for 3 consecutive months. Hope this explains my problem and greatly hope that there is a solution. Formating indivdual cells or columns does not work as I do not know which ones will be affected and therefore need to be flagged. Its a random kind of thing...............

 

[SkipVought]

Post some sample data and explain how you want to formatting to occur

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[bdm1]

Hi Skip, I will post a samble as soon as I get a chance. Series of meetings in next few days will probably pre-empt this project for a bit.....