Change the color of a cell based on another cell

Question

Hello,I'm looking to create a conditional rule that would color my cell (R3) if it contains the data entered in Q3 and/or in P3.Additionally, I would like to stretch this rule for the entire R column to encompass the full list of rows (over 110,000 rows).I need to perform a validation (quality control) on the data of a massive file to be processed.Thanks a lot!Configuration: Windows / Chrome 61.0.3163.100

Mike-31 · Answer

Hello,  if I understood correctly, select R3 and Conditional formatting the formula is =AND(R3<>"";OR(R3=P3;R3=Q3))  choose the fill color  -- A+ Mike-31  I am responsible for what I say, not for what you understand...

Emilie9457 · Answer

small clarification: the data in R3 is larger than in P3 / Q3... for example:  R3: hello, my name is Pierre Poiré blablabla P3: Pierre Q3: Poiré  I want to find a way to validate if the data in P3 and/or Q3 is found within the data in R3.  Thanks!

Mike-31 · Answer

Re,  the basic formula is to count if a name appears in the string in R3 with a simple COUNTIF =COUNTIF(R3,P3) if the name were at the beginning of the string one would use a wildcard * =COUNTIF(R3,P3"*") if the name were always at the end of the string =COUNTIF(R3,"*"&P3) since the name can be at the beginning, in the middle, or at the end of the string, we use two wildcards =COUNTIF(R3,"*"&P3"*")  From there, we can play with the formula to test if Pierre is in R3 =IF(COUNTIF(R3,"*"&P3"*"),"Pierre is in R3","Pierre is not in R3")  to test if Poiré is in R3 =IF(COUNTIF(R3,"*"&Q3"*"),"Poiré is in R3","Poiré is not in R3")  to test if either of the two is in R3 =IF(OR(COUNTIF(R3,"*"&P3"*")>=1,COUNTIF(R3,"*"&Q3"*")>=1),"Pierre or Poiré are in R3, see both","Neither of them is in R3") -- A+ Mike-31  I am responsible for what I say, not for what you understand...

Change the color of a cell based on another cell

3 réponses

Texture issues in enshrouded

Old lullaby

Unable to connect to freebox os

Edit ad on leboncoin for free

Freebox wifi password

Computer tower that won't turn on

Blocked: are my messages still being delivered?

Video intercom inspector

Your computer can't use another display.

Fransat decoder stuck