Conditional formatting from seperate sheet

[RJB78]

Hello -

I am looking to use conditional formatting to highlight a cell red.

I have 2 columns of text on separate sheets. If the text is found in column A on sheet 1, and its also found in column A on sheet 2 - highlight the sheet 1 text red

I tried to use a formula to determine what cells to highlight =$A5=LP!$A$4:$A$220 is the formula I am using but its not working. 'LP' is the name of the other sheet.

Thank you for your help.

 

[oldbrewer]

something like =if(iserror(search($a5,lp!$a$4:$a$220)=false

 

[RJB78]

@oldbrewer

That formula didn't work for me.

 

[oldbrewer]

found out it is not allowed to reference other sheets in conditional formatting

so on sheet1 HZ1 put =sheet2!A1
and drag down

then reference HZ1:Hz10 in the conditional formatting

 

[RJB78]

Thank you oldbrewer

 

[oldbrewer]

you are welcome I learned about the restriction with conditional formatting