• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

I want to color code certain cells based on 2 to 6 vertical cells

Hi Larry ,

To check for the pattern 2 , 2 , 6 , 6 , your 4 rules would be , with the data range being $B$7:$B$66 ( you need to select this data range first ) :

=AND(B4=2, B5=2, B6=6, B7=6)

=AND(B5=2, B6=2, B7=6, B8=6)

=AND(B6=2, B7=2, B8=6, B9=6)

=AND(B7=2, B8=2, B9=6, B10=6)

Narayan
 
OMG, please tell me why this is not working. I did exactly what you said but somehow I must be something wrong. This is my 3000 sheet row. I high lighted the entire R column and placed the following CF

=AND(R4=2, R5=2, R6=6, R7=6)

=AND(R5=2, R6=2, R7=6, R8=6)

=AND(R6=2, R7=2, R8=6, R9=6)

=AND(R7=2, R8=2, R9=6, R10=6)

It is only shading 4 cells out of 1300,, note one after the 6622 did not shade

I an upload the file to box.net if need be but his is how i entered it
 

Attachments

  • 1 set only.jpg
    1 set only.jpg
    371.7 KB · Views: 3
Hi Larry ,

The crucial point here is what range did you select before you applied the CF rules ?

Now that you have uploaded the pic of the CF rules , it is clear !

Change all the $R$5 to $R$7. Your data range is supposed to start from R7 , which is why your rules will start from R7 and go upwards to R6 , R5 and R4 because there are 4 rules.

The problem now is because we have used relative referencing , the moment you change the $R$5 to $R$7 , all the rules will be changed automatically !

You will need to edit each rule again to change them to the correct references.

Narayan
 
NARAYANK991 said:
Hi Larry ,

The crucial point here is what range did you select before you applied the CF rules ?

Now that you have uploaded the pic of the CF rules , it is clear !

Change all the $R$5 to $R$7. Your data range is supposed to start from R7 , which is why your rules will start from R7 and go upwards to R6 , R5 and R4 because there are 4 rules.

The problem now is because we have used relative referencing , the moment you change the $R$5 to $R$7 , all the rules will be changed automatically !

You will need to edit each rule again to change them to the correct references.

Narayan
Click to expand...


hallelujah !!!!!!! This has been driving me crazy for over an hour

THANK YOU THANK YOU THANK YOU!!!
 
Hi Larry ,

From the uploaded pic , I am not able to understand how you have formulated the rules ; first , for the RED on GREEN , you should have 4 rules ; then for the BLUE on YELLOW you should have 4 rules.

I assume that one set of rules is for 6 , 6 , 2 , 2 ; the other set of rules would be for 2 , 2 , 6 , 6.

The set of rules for 6 , 6 , 2 , 2 would be as follows :

=AND(B4=6, B5=6, B6=2, B7=2)

=AND(B5=6, B6=6, B7=2, B8=2)

=AND(B6=6, B7=6, B8=2, B9=2)

=AND(B7=6, B8=6, B9=2, B10=2)
.......................................................................................................

The set of rules for 2 , 2 , 6 , 6 would be as follows :

=AND(B4=2, B5=2, B6=6, B7=6)

=AND(B5=2, B6=2, B7=6, B8=6)

=AND(B6=2, B7=2, B8=6, B9=6)

=AND(B7=2, B8=2, B9=6, B10=6)
.......................................................................................................

Narayan
 
Narayan,

I worked on trying to solve my issues for a few hours last night and I can not figure out why the 6622 or 2266 on my 3000 row it does not. Here is how it looks in the long sheet and again thanks for your patience with me

I ave tried it this way =$R$7:$R$3001

=AND($R6=2, $R7=2, $R8=6, $R9=6)

=AND($R7=2, $R8=2, $R9=6, $R10=6)

=AND($R8=2, $R9=2, $R10=6, $R11=6)

=AND($R9=2, $R10=2, $R11=6, $R12=6)

and also this way



=AND($R4=2, $R5=2, $R6=6, $R7=6)

=AND($R5=2, $R6=2, $R7=6, $R8=6)

=AND($R6=2, $R7=2, $R8=6, $R9=6)

=AND($R7=2, $R8=2, $R9=6, $R10=6)


346works fine

=AND($R5=6, $R6=4, $R7=2)

=AND($R6=6, $R7=4, $R8=2)

=AND($R7=6, $R8=4, $R9=2)
 

Attachments

  • 6622 and 346.jpg
    6622 and 346.jpg
    382.7 KB · Views: 1
  • 246 Works 6622 no.jpg
    246 Works 6622 no.jpg
    88.7 KB · Views: 1
Hi Larry ,

If you select the range R7 through R3001 , and then apply the following rules :

=AND($R4=2, $R5=2, $R6=6, $R7=6)

=AND($R5=2, $R6=2, $R7=6, $R8=6)

=AND($R6=2, $R7=2, $R8=6, $R9=6)

=AND($R7=2, $R8=2, $R9=6, $R10=6)

it has to work. I have tried it and it works.

This is for the sequence 2 , 2 , 6 , 6 with 2 at the top of the sequence , and 6 at the bottom of the sequence.

In order to color the sequence 6 , 6 , 2 , 2 , just interchange 2 and 6 in the above 4 rules.

Narayan
 
Well Narayan! I have no clue what's going on but I will figure it out. On a new sheet is works great. On my 3001 row sheet t does not. All the data is copied the same so I'm not sure what may be interfering with the CF. I appreciate your help tremendously
 
Narayan I wanted to let you know For some reason I couldn't get it to work on my laptop which has windows 8 and office 2013. I moved to my desktop which has Windowa 7 office 2010 and it's working fine I'm going to reinstall office today on my laptop but I've had issues with 2013 office since day 1 with certain files. Thanks for all you've done and your help. Your amazing!!!
 
I have completed all my long sheets (3000 rows) and tey all work great THANK YOU!!!

On my short sheets the rows start at row 2 and on my long sheets I started at row 5. What adjustments do I need ot make to copy and paste from the long sheet to the short?

For example on my long sheets I used:

=AND($B4=6, $B5=6, $B6=2, $B7=2)

=AND($B5=6, $B6=6, $B7=2, $B8=2)

=AND($B6=6, $B7=6,$B8=2, $B9=2)

=AND($B7=6, $B8=6, $B9=2, $B10=2)

should I change it to:

=AND($B2=6, $B3=6, $B4=2, $B5=2)

=AND($B3=6, $B4=6, $B5=2, $B6=2)

=AND($B4=6, $B5=6,$B6=2, $B7=2)

=AND($B5=6, $B6=6, $B7=2, $B8=2)

Thank you!!
 
Hi Larry ,

If your data is going to start from row 2 , then it is better to change the logic so that we look at the rows below the starting row , instead of looking at the rows above.

Probably this will look simpler to you. The formula remains the same in all 4 CF rules , but the range changes.

Thus to check for the sequence 6 , 6 , 2 , 2 , first select your range B2:B3001. Note that the range will change for each rule , so you need to exit the CF dialog after applying each rule and select the new range for the next rule and re-enter the CF dialog.

The steps will be :

1. Select the range B2:B3001.

2. Apply the first rule , which will be : =AND($B2=6, $B3=6, $B4=2, $B5=2)

3. Exit the dialog

4. Select the range B3:B3001.

5. Apply the second rule , which will be : =AND($B2=6, $B3=6, $B4=2, $B5=2) i.e. the same as the first rule.

6. Exit the dialog

7. Select the range B4:B3001.

8. Apply the third rule , which will be : =AND($B2=6, $B3=6, $B4=2, $B5=2) i.e. the same as the first rule.

9. Exit the dialog

10. Select the range B5:B3001.

11. Apply the fourth rule , which will be : =AND($B2=6, $B3=6, $B4=2, $B5=2) i.e. the same as the first rule.

12. Exit the dialog

In case you have only 3 or 2 rules , you know what to do.

In case you have a sequence of more than 4 digits , just extend the above to cover the additional digits in the sequence ; remember , in this set up , the rules are identical ; the range changes in each rule.

Narayan
 
Thanks Narayan, what I have been doing is copying the entire column format eh 3301 and pasting to the 100 row and then changing the 3001 to 100. I will see which way works best

Thanks
 
You must log in or register to reply here.
Back
Top