I have a table B2:H9 and I'm trying to highlight the cells that match the values in column K2:K9 see sample 1
I can do conditional formatting in B2:H9 cells by using the EQUAL TO option - I select one cell in the column K2:K9 at a time, so I have to repeat the process 8 times.see sample 2
The problem I am running into is that both the table B2:H9 and the reference column K2:K9 will change size (sometimes will be 1000 rows or more) and it's impossible to repeat this task so many times.
Is there a faster way to do this? I tried the option "Use a formula to determine which cells to format" but had no luck - it seems to only work for individual cells but not for the full column K2:K9
Thanks!
CodePudding user response:
you can do the following:
- select B2
- set conditional formatting as
=B2=$K2
, press OK - in the
Applies to
section extend the range from B2 to B2:H9
as for B2 there is no $ it should change dynamically for both rows and columns, and for $K2 only row should change