How can you highlight an entire row based on a single condition in another column?
To highlight an entire row based on a value in a column using conditional formatting requires using the
A spreadsheet contains the following simple data where the first column contains a list of dates and the other columns contain corresponding data for that date.
Here’s a snapshot of the spreadsheet which contains
Bank Pmt Id
The simple spreadsheet has a payment schedule for each calendar day of the year and the corresponding interest charge, rates, principal amount and balances.
To highlight an entire row to check how the repayments are going a conditional format was placed on the entire sheet.
To do this, first highlight all cells in the active sheet.
Then click on the menu item Format then Conditional formatting .
With the conditional formatting sidebar that pops up click on the first dropdown menu labelled
Format cells if...
As previously taught regarding conditional formatting and custom formulas the principles from this article help in easily creating a formula matching the requirement.
The custom formula is:
The custom formula for the conditional format contains two
formulas. The inner
formula represents the
as it loops through the entire highlighted range.
formula references the required
so that the comparison can be made.
Finally, the condition
checks if the value in column A matches today’s date. If this is
then the conditional formatting will apply the formatting set.
Here’s how the spreadsheet looked:
As you can see the custom formula inserted into the custom formula input box produces the highlighted row as it matches today’s date (don’t use
as this includes time).
To create a conditional format that highlights an entire row based on a column in that range meeting a specific condition use the
formula to first grab the active cell (using
) and then use that in an outer
function to reference the specific column name along with
As the condition for this specific requirement was to have the first column in the sheet match today’s date the custom formula ended up being: