Google Sheet Conditional Formatting highlight 2 columns if one column contain certain text of 5 columns

Question

In Google Sheet, I want to highlight only 2 columns out of 5 columns.

5 columns here but I want to highlight only 'Name' and 'Weight' columns if a cell contain the word 'Smith'

The outcome should be like this.
I want to input more name and if the name contain the word 'Smith', I want it to be automatically highlighted for name and weight columns.

I tried to use conditional formatting in Google sheet, and I could highlight only the name column.
This is what I tried.

Outcome was this.

Answer 1

You are not far, try the following formula in the conditional formatting:

=IF(REGEXMATCH($C3, "Smith"), 1, 0)

Answer 2

You can try the following code:

function highlight() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var data = ss.getRange('A1:E').getValues();
  for (i = 1; i < data.length; i++) {
    var tf = ss.getRange("B" + i).createTextFinder('smith');
    tf.matchEntireCell(false);
    tf.matchCase(false);
    var result = tf.findNext();
    if (result !== null) {
      var range = result.getRow();
      ss.getRange('B' + range).setBackground('Yellow');
      ss.getRange('D' + range).setBackground('Yellow');
    }
  };
};

Answer 3

The formula given by @nabais works.

In conditional formatting though one does not need to use the starting IF function.

"Format cells if" is how conditional formatting rules are formed by default as noted in the official help page.

4. Create a rule.

• Single color: Under "Format cells if," choose the condition that you want to trigger the rule. Under "Formatting style, choose what the cell will look like when conditions are met.
• Color scale: Under "Preview," select the color scale. Then, choose a minimum and maximum value, and an optional midpoint value. To choose the value category, click the Down arrow Down Arrow.

So the following formula is all that is needed:
(Please adjust ranges to your needs)

=REGEXMATCH($G2, "Smith")