Search code examples
google-sheetscustom-formatting

How to do conditional formatting for a table based on values in separate columns?

I have a table that I am trying to have each row highlight based on a blank cell in one column AND a value in another column. I want each row to look for a blank cell in column K and a value of 10 in column C. Long form explanation is: If the cell in column K is blank, AND the cell in column C is 10, then highlight that entire row.

This is what I could come up with but it highlights random rows so I know it's off: =AND($K2="",$C2="10")

I'm also unsure what to select for that range, I selected the entire table

Thank you!

Solution

  • You can use this:

    Range: A1:O

    Custom Formula:

    =AND($K1="",VALUE($C1)=10) or =AND($K1="",$C1="10")

    Output:

    enter image description here

    Not sure why, but your Column C value was eveluated as a text and not a number. To make some workarounds, I used VALUE() to convert text/string to a number first before comparing.

    Please make sure not to remove $ in the formula. It is important to lock the columns to K and C and just increment the row number. If the $ is not included, conditional formatting will loop all rows and columns in the given range

    Note:

    The reason why your formula failed initially because it doesn't match with your range. Since your range started at Row 1, your formula should also start with Row 1.

    What happened in your initial configuration is that, when checking row 1 if it should be highlighted, It checks the value in K2 and C2 so on....