Highlight cell from number

[Jack-P-Winner]

I have 5x7 , 5X12 and 5x15 grids that are laid out 5 across and 7-12-15 down. Is it possible for me to place numbers in EFGHIJ and the grids across from N:BT get high lighted with a different color that matches the numbers in EFGHIJ. If I have to do all the same color that will be fine too.

I will have these grids down to row 100 and on separate sheets for example sheet 1 will have the 5x7, sheet 2 will have the 5x12 and sheet 3 will have the 5x15

Is this possible?

Thank you!!!

 

[Hui]

You can use Conditional formatting
Select N5:R12
Conditional Formatting
New Rule
Use a Formula
=COUNTIF($E$5:I5,$N$6)>0
select a Format
Apply


Repeat for all other areas

 

[Jack-P-Winner]

That actually highlights the entire row. I want to do individual cells in each row

 

[Somendra Misra]

Hi Jack,

Try this:

Select N6:R12, in CF formula put below formula
=COUNTIF($E$5:$I$5,N6)

Repeat for all the blocks, change $E$5:$I$5 to $E$6:$I$6 i.e. increase row number as you go from left to right on the blocks and Change N6 to first cell of each block.

Regards,

 

[Jack-P-Winner]

Thanks Misra, on iPad at the moment and will try from here f not def report back this afternoon. THANK YOU!

 

[Jack-P-Winner]

Well, no conditional formatting from iPad )-:

just to be clear

Imagine E:J 5 empty all the way down to last row. The 5x7 grids have no colors only numbers

I want to be able to place numbers in EFGHIJ and whatever number I place in those cells gets highlighted a specific color. Will that do what your suggesting?

Thanks

 

[Hui]

Whoops
Try this:
Select N5:R12
Conditional Formatting
New Rule
Use a Formula
=COUNTIF($E$5:$I$5,N6)>0
select a Format
Apply

Repeat for each area, adjust accordingly

 

[Jack-P-Winner]

Thanks Hui, should I clear out the rule mentioned in #2 post?

Also curious, can this be written with a Macro or Dynamic coding?

 

[Hui]

Yes, There shouldn't be any other rules I believe

You could write a generic function to do all the CF's but I don't have time to resolve that

 

[Jack-P-Winner]

when I try to go down to the next group I get an error and have no clue why. The only thing I am changing is the row number

 

[Jack-P-Winner]

Okay, I have been trying to write this and changing things up and still cannot seem to get it right. I want what is in CDEFGH to hilite say yellow on all the C1, C2,C3,C4,C5,C6 FOR ROWS 2-10

Orange for rows 12-20
Light Green for 22-30
Light blue for 32-40
Tan for 42-50

If someone can get me started I can finish them
I have tried to select my range as L-CB rows 2-10 and tried =COUNTIF($L$2:$CB$10,C2)>0 and I can not get it to work. Thanks for the help folks

 

[Somendra Misra]

@Jack-P-Winner

See the attached file. I had done for first block, is this what you want? And I think you had inserted an extra C1-C6 block.

Regards,

 

[Jack-P-Winner]

YAY!!!!!! YES YES YES, THANK YOU!!!!

 

[Jack-P-Winner]

When I do the next section it does not work so what am I doing wrong?

=COUNTIF($C$12:$H$12,L20)