So I have two sheets together and I'm trying to change the colour of a cell in one sheet based on if a range in the other sheet contains a certain value.
For example, in sheet 1 I would like cell A1 to turn red if the word "cat" occurs in a cell in the range A1:A20 in sheet 2.
I feel like this should be possible, but I'm not certain. Thanks for any help.
Edit: I was able to get the sheet1 cell to turn colour if a specific cell in sheet2 has the right word using =INDIRECT("Sheet2!A3")="cat". I tried changing the A3 to A1:A20 to search that range for the word cat but it didn't work.