[Solved] Conditional Formatting for an entire row

[Phantom5389]

Hi. Noob here, this is my first post.
I have done some reading on conditional formulas and I just can't seem to find the answer I am looking for or am I looking in the wrong place?
I have a spreadsheet setup (8 colums and numerous rows).
Most of the comments to be put into the cells is a 'Yes' or 'No'. What I am trying to setup is a formula or something that will colour automatically fill the entire row red/green whatever colour if any of the cells in that row has a 'No' in it.
Any help would be appreciated.

Thanks
Steve

[crusader]

For individual cells: Format -> Conditional Formatting.
You may need code if you want to color the entire row.

[thinman3]

I yield to Mr. Programmers' solution...original post deleted.

thinman3

[MrProgrammer]

Hi, and welcome to the forum.

You may need code if you want to color the entire row.

No code is needed. See attached example.

I typed A2:H28 in the name box of the formula bar, and used Format → Conditional Formatting → Formula Is → COUNTIF($A2:$H2;"No")>0 to give this condition a style with a cell background color. I'm done in one step.

Read section 8. Using formulas and cell references in Ten concepts that every Calc user should know if you don't know what the $'s in $A2:$H2 mean. Note that there is a $ before the column letters but no $ before the row numbers.

You could help other users of the forum if you go to your first post and change the title from "Formulas for an entire row" to "Conditional Formatting for an entire row", which I think will be easier to find for people with similar situations.

If this answered your question please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Phantom5389]

Thanks for that Mr. Programmer it has helped and works a treat.

I might see if I can add other formulas for other conditions in the same spreadsheet.

Reading through Ten Concepts, thanks again for your help.

Cheers
Steve

[carmelchas]

I figured out a way to do what you would like. Below is my attempt to give you a step-by-step solution.

In this example, I wanted to use VLOOKUP to determine a relative value in order to determine the desired format relative to the row.

1. Highlight a range of cells in the first row in the desired range of cells.
2. In the main menu, click "Format".
3. In the sub-menu, click "Conditional Formatting".
4. In the "Condition 1" select "Formula is" instead of "Cell value is".
5. Enter the desired formula; In my case [ VLOOKUP($B3;Sheet2.$A$2:$C$890;3)="CONFLICT" ]
6. Select the desired style for the case where the condition evaluates to TRUE.
7. Click "OK"; This applies the formatting to the first row if the condition is true.
8. Highlight the same range of cells again.
9. Right-click on the selected range of cells.
10. Click "copy" in the context menu that appears.
11. Highlight the remaining row-cells.
12. Right-click on the selected range of cells.
13. Click "paste special" in the context menu that appears.
14. Uncheck the "Paste all" checkbox.
15. Make sure that the only "format" checkbox is checked.
16. Click "OK".

DONE.

Special Note: If you are using a formula, you will have to make sure to use ABSOLUTE cell references and RELATIVE cell references where applicable in order to ensure the formula maintains the relative-integrity desired with respect to the row.

Cell Referencing (just in-case):
=============================
$A$1 == Reference to the cell located in the Absolute column A in the Absolute Row 1
$A1 == Reference to the cell located in the Absolute column A in the Relative Row 1
A1 == Reference to the cell located in the Relative column A in the Relative Row 1
A$1 == Reference to the cell located in the Relative column A in the Absolute Row 1