CODEWITHSUNDEEP
excelconditional-formatting
Lelantos
Lelantos

Reputation: 119

Excel conditional for rows according to multiple cell values

So I'm looking to get a formula that will set the row to be white/orange/green depending on the amount of cells populated in the row.

each cell with have different data, some text some numerical. It just needs to detect if the cell has been populated/filled with any data.

Example A12 + B12 = White

A12 + B12 + C12 + D12 = Orange

A12 + B12 + C12 + D12 + E12 + F12 = Green

Hope this is clear enough. Thanks DG

Upvotes: 0

Views: 97

Answers (2)

pnuts
pnuts

Reputation: 59442

I suggest select entire spreadsheet and HOME > Styles - Conditional Formatting, New Rule..., Use a formula to determine which cells to format and Format values where this formula is true::

=COUNTA(1:1)=2

Format..., select White Fill (if you must, another colour not Orange or Green might be more suitable) OK, OK.

Then repeat with:

=COUNTA(1:1)=4

and Orange, and with:

=COUNTA(1:1)=6

and Green.

Upvotes: 0

Kerry White
Kerry White

Reputation: 416

If you're just looking at if it is populated or not, you'll probably want to break it up into three separate conditions Like this:

Create a new Conditional Formatting Rule that has this in the formula: =COUNTA(A12:F12) = 2 and set the format as you want. Create another one that has =COUNTA(A12:F12) = 4 and set the format as you want. Create another one that has =COUNTA(A12:F12) = 6 and set the format as you want.

To highlight the whole row, you need to set the formula to absolute: =COUNTA($A$12:$F$12) = 2 and set the "applies to" to something like =$A$12:$AA$12 ... or where ever the end of your row is.

Upvotes: 1

Related Questions