Sorry about the first post (I edited after I posted so it is correct now), I hit enter on the keyboard and it submitted it before I was finished typing everything.
I have 2 worksheets. One has locations with numbers, example:
275 Location 1
276 Location 2
I have all my locations on the second sheet, but in 2 different columns, listed with numbers only, example:
I have 2 scenarios I need help with.
If any of the numbers on sheet 1 match the numbers in column 1 on sheet 2, highlight the number on sheet 1 in green
If any of the numbers on sheet 1 match the numbers in column 2 on sheet 2, highlight the number on sheet 1 in red
If the number on sheet 2 matches any number on sheet 1, highlight green
I want these to apply to all the cells that have numbers (it could apply to all cells I guess as it should ignore it if it doesn't match, I would assume)
First name each column... so select the range in the first column of Sheet2 and name it something like List1 (you do this by typing List1 in the Name box, just to the left of the Formula bar at the top). Name the other column List2, and the column on sheet1, List3.
Then select the List3 range, and invoke conditional formatting.
Select to "use a formula to determine which cells to format" and then enter formula:
where A2 is top-most cell in List3 selected.
Click Format and choose from Fill tab (green)
Click New Rule and repeat with formula:
click Ok, and ok again to finish... go to the other sheet and select both columns, and invoke conditional formatting following same rules as above, but with formula: =Match(A2,List3,0) where A2 is topleft most cell in selection....
Microsoft MVP - Excel
Actually it looks like it applied the first rule to not everything. Not sure why. For instance, in column B on sheet 2 there is number 325 and on sheet1 there is a 325 and nothing is applied to it. I may just have to go through this manually.
I deleted all the rules and redid them, I also formatted all the cells the same before I applied the rules and the first 2 are doing what they are suppose to, but I cannot get rule 3 to do anything.
Sorry that is my mistake... MATCH() only works on one row or column
Microsoft MVP - Excel
There are currently 1 users browsing this thread. (0 members and 1 guests)