Highlighting a cell on a table

[mrc44]

I have a table in the excel file attached. What I need is when I enter a value in A1, B1,C1,D1 etc. if the value is present in the corresponding table row, it should be highlighted. I made an example in the attached file. https://1drv.ms/x/s!AoGkZUHlKui9gRg6qf_Yom2t6eig

 

[wideboydixon]

Conditional formatting is your friend here. Highlight J1:R4 and add a new conditional formatting rule based on the following formula:

=COUNTIF($A$1:$I$1,J1)>0

Set the format to be green background fill.

WBD

 

[mrc44]

Thanks. Yes your formula works in this case only if I use a single row $A$1:$I$1 in this case. If I want to use more rows, I think it won't be practical. Is there a formula for this kind of task that I can copy and paste across ?

 

[wideboydixon]

Just extend the first parameter to the range where you might type values. E.g.

=COUNTIF($A$1:$I$4,J1)>0

WBD