I had an interesting question about conditional formatting posed in the comments by Stephen.
In a new sheet, I am trying to make a whole row turn red, green or amber depending on the value of one cell in that row, so I can easily see which jobs we have won, lost or are pending. Any 'IF' conditional formula I write gets thrown out by Excel. What am I doing wrong?
I promised him an answer so here it is.
For this exercise I am making some assumptions.
The first thing I did was set up a spreadsheet using column H as my rule column. I often do something similar to this where I create a hidden column at the side of my data table and perform a IF function based calculation that returns me a value depending on the data evaluated. To use Stephens example I am assuming that jobs won have returned a 1, jobs pending returns a 0 and jobs lost a –1. These values can be anything – they are not relevant.
Now we are ready to create the conditional formatting rules.
The important thing to remember with conditional formatting when trying to work with more than just the selected cell is the absolute and relative cell selection rules. Read more about absolute and relative references if you need to.
Select the entire sheet (by clicking the gray triangle left of column A).
From the Home tab select the conditional formatting option and click Manage Rules.
This brings up the Conditional Formatting Rules Manager.
Select New Rule and then Use a formula to determine which cells to format.
Create a rule where the formula is =$H1=-1 and the format is a fill of red.
Now make a new rule where the formula is =$H1=1 and the format is a fill of green.
Lastly make a new rule where the formula is =AND($H1=0,$H1<>””) and the format is a fill of amber. This rule keeps the cells unfilled where there is nothing in column H.
Click Apply to view the result.
The trick here is that the cell reference $H1 allows the rule to be applied down to every row and use the value in column H for every cell in that row.
Columns could be formatted in the same manner using a rule value in a row by locking the row and allowing the column to alter. eg. H$1.
Feel free to comment if this was helpful, or if you have other questions that need solving.