CODEWITHSUNDEEP
excelconditional-formatting
yarnabrina
yarnabrina

Reputation: 1666

Colour rows of an entire sheet based on values in a column

I have a Excel file of daily expenses, with each row containing records of a single transaction.

Consider the following:

A: Date of transaction B: Received/Spent C: Payer/Payee D: Details E: Amount

The B column can have entries of these types:

  1. Spent by Cash
  2. Spent from Wallet
  3. Spent by Check

(and similarly for Received)

I want to colour the columns A-E by Red or Green, according as the transaction is Spent or Received.

I can detect whether a particular cell contains a particular word or not, using ISNUMBER(SEARCH(word, cell)), how to use conditional formatting on a particular based on this.

But I do not know how to apply this to the entire sheet, except the top row.

Any help will be highly appreciated.

Upvotes: 0

Views: 60

Answers (2)

pnuts
pnuts

Reputation: 59460

Once you have it working for one row you have the necessary formula - just adjust the Applies to range.

Upvotes: 0

Mikku
Mikku

Reputation: 6654

Like this:

  • Select all data
  • Goto Conditional Formatting
  • Select New Rule
  • Enter the Formula as per Screenshot.
  • Repeat for other Keywords.

enter image description here

Use this formula to get all the Spent in One go : =ISERROR(FIND("Spent",$B2))=FALSE

enter image description here

Upvotes: 3

Related Questions