0

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

Improve this question

2 Answers 2

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

And how does one apply these colour names to target cells as a fill colour?
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.
I thought it'd have to be something like that, since the formula in your answer isn't actually usable for conditional formatting.
Well, for it to be applicable, the bit about the Conditional Formatting Rule should be in the answer, not the formula that just returns names of colours. Also, it would be worth considering how to do this if the OP wants to highlight the entire row, as currently I think relative referencing would break it for anything except the first cell to which the formatting was applied.
Correct. So, an answer should reflect these and any other steps of how it would actually be done, rather than just a conceptual sketch as currently.
|
0

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.

Improve this answer

1 Comment

sorry, how would this colour the entire row? I can see how this would colour the cells, but not how it would colour the row?

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.