excel conditional formating

donnylad · 28 Nov 2018

Hi to all,,, I am using excel 2016 and I have a 2 rows of data of which the top row consists of W or L and the row underneath consists of numbers that can be a number from 1 to 8.
If the top cell is an L and the cell below is >6 I want the selected cell to be formatted with a red fill.
I can do it on just one cell with this formula: =AND(IF($a$1="L",1),IF($a$2>6,1)) which works just fine but I have a lot to do on 2 sheets which will take forever, can someone please tell me a formula that lets me select a full row to check the row below and only formats the cells that have L above and >6 below.

Len

Try3 · 28 Nov 2018

Len,

You can take out the $ sign next to the cell references that you want to act relatively.

So, in this case, remove the $ to the left of each a [replace $a with a]. You can then copy or drag the cell to the right and the formulas will automatically adjust to
=AND(IF(b$1="L",1),IF(b$2>6,1))
=AND(IF(c$1="L",1),IF(c$2>6,1))
and so on

Denis

donnylad · 28 Nov 2018

Thankyou very much for your reply Denis but, as I have already have formulas in the cells I have had to use conditional formatting (HOME, CONDITIONAL FORMATING, NEW RULE, "Use a formula to determine which cells to format") , and it is there where I put my formula.
Well, while waiting for an answer I selected a cell that I had put my formula and looked about and in (MANAGE RULES) it shows my formula and it also says "Applies to". So I input =A$1:E$1 ,clicked on apply and there you are, it had done the whole row. So thankyou again and I think this will be helpful to others with the same problem.
Len