Possible formula or function

%3CLINGO-SUB%20id%3D%22lingo-sub-2165689%22%20slang%3D%22en-US%22%3EPossible%20formula%20or%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2165689%22%20slang%3D%22en-US%22%3E%3CP%3EIs%20there%20a%20formula%20or%20function%20available%20that%20highlights%20a%20box%20containing%20a%20certain%20number%20when%20this%20number%20is%20type%20in%20another%20box%26nbsp%3B%20in%20the%20document%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EContext%3A%20This%20is%20for%20our%20in-office%20lottery.%20I%20would%20like%20the%20boxes%20containing%20the%20weekly%20numbers%20to%20automatically%20by%20highlighted%20when%20I%20enter%20them%20in%20the%20spreadsheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22AnnDegrace_0-1614257740493.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F257845iB6E0AD8E59884FD3%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22AnnDegrace_0-1614257740493.png%22%20alt%3D%22AnnDegrace_0-1614257740493.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2165689%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2167304%22%20slang%3D%22en-US%22%3ERe%3A%20Possible%20formula%20or%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2167304%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F978571%22%20target%3D%22_blank%22%3E%40AnnDegrace%3C%2FA%3E%26nbsp%3BIf%20I%20understand%20correctly%2C%20you%20want%20the%20cells%20in%20B1%20(or%20cell%20where%20the%20first%20number%20of%20the%20first%20employee%20is)%20through%20G4%20or%20cell%20where%20the%20last%20number%20of%20the%20last%20employee%20is)%20to%20highlight%20yellow%20if%20the%20number%20in%20the%20cell%20is%20found%20in%20J1%20(or%20first%20cell%20you%20enter%20a%20number%20in)%20through%20N4%20(or%20last%20cell%20you%20enter%20a%20number%20in).%20I'm%20not%20certain%20if%20the%20grid%20to%20the%20right%20is%20located%20in%20another%20worksheet%2C%20or%20if%20it's%20relative%20to%20the%20discussion%2C%20but%20similar%20instructions%20can%20be%20used.%20You%20want%20to%20use%20Conditional%20Formatting%20on%20the%20Home%20tab.%20Select%20all%20of%20the%20cells%20you%20want%20to%20change%20color%2C%20then%20click%20Conditional%20Formatting%2C%20then%20select%20New%20Rule%2C%20then%20click%20on%20Use%20a%20formula%20to%20determine%20which%20cells%20to%20format.%20In%20the%20box%20that%20says%20Format%20values%20where%20this%20formula%20is%20true%3A%20enter%20%22%3DCOUNTIF(%24J%241%3A%24N%244%2CB1)%26gt%3B0%22%20without%20the%20%22.%20The%20%24J%241%20should%20be%20the%20top%20left%20cell%20you%20will%20be%20entering%20the%20numbers%20in%2C%20and%20%24N%244%20should%20be%20the%20last.%20B1%20will%20be%20the%20top%20left%20cell%20of%20your%20employee%20numbers.%20Next%20click%20the%20Format%20button%2C%20select%20the%20Fill%20tab%2C%20and%20choose%20your%20color%2C%20then%20click%20OK%2C%20and%20then%20OK%20again.%20If%20you%20want%20the%20grid%20to%20highlight%20also%20follow%20the%20same%20instructions%2C%20except%20you%20will%20select%20the%20grid%20instead%20of%20the%20employee%20numbers%2C%20and%20the%20B1%20in%20the%20formula%20will%20be%20the%20top%20left%20cell%20of%20the%20grid.%26nbsp%3B%20I%20hope%20this%20helps.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Is there a formula or function available that highlights a box containing a certain number when this number is type in another box  in the document?

 

Context: This is for our in-office lottery. I would like the boxes containing the weekly numbers to automatically by highlighted when I enter them in the spreadsheet.

 

AnnDegrace_0-1614257740493.png

1 Reply

@AnnDegrace If I understand correctly, you want the cells in B1 (or cell where the first number of the first employee is) through G4 or cell where the last number of the last employee is) to highlight yellow if the number in the cell is found in J1 (or first cell you enter a number in) through N4 (or last cell you enter a number in). I'm not certain if the grid to the right is located in another worksheet, or if it's relative to the discussion, but similar instructions can be used. You want to use Conditional Formatting on the Home tab. Select all of the cells you want to change color, then click Conditional Formatting, then select New Rule, then click on Use a formula to determine which cells to format. In the box that says Format values where this formula is true: enter "=COUNTIF($J$1:$N$4,B1)>0" without the ". The $J$1 should be the top left cell you will be entering the numbers in, and $N$4 should be the last. B1 will be the top left cell of your employee numbers. Next click the Format button, select the Fill tab, and choose your color, then click OK, and then OK again. If you want the grid to highlight also follow the same instructions, except you will select the grid instead of the employee numbers, and the B1 in the formula will be the top left cell of the grid.  I hope this helps.