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.


1 Answer 1


You can format cells depending on whether the range Sheet2!A1:A20 contains cat with a conditional formatting custom formula rule like this:

=counta( iferror( filter( indirect("Sheet2!A1:A20"), indirect("Sheet2!A1:A20") = "cat") ) )

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.