Search code examples
google-sheetsgs-conditional-formatting

Conditionally color Google Spreadsheet cell if value matches different column in same row

I have a Google Spreadsheet with data values in the first 10 columns (A through J) and the 11th column (K) has a reference value. This pattern repeats for 150 rows

So, for the first row, A1 through J1 are the data values and K1 is the reference value. And for the 25th row, A25 through J25 are the data values and K25 is the reference value.

Now, for any data cell (cells within the first ten columns), where the value in the cell matches the value in the corresponding reference cell (at column K in the same row as the data cell), I want to change the background color of the data cell.

So, for data cell A1: if value in A1 == value in K1, change background color of A1 And, for data cell C23: if value in C23 == value in K23, change background color of C23

I assume that conditional formatting should be able to do this, but I could not figure out how. I know how to do conditional formatting for a single cell, but I can't figure out how to apply conditional formatting to the entire table (cells A1 through J150) at once.

Solution

  • Use the following custom formula in conditional formatting:

    =A1=$K1

    and apply to range A:J.

    To exclude blank cells (and cells with zero-length text strings), try:

    =AND(LEN(A1),A1=$K1)