excel conditional formating


  1. Posts : 166
    microsoft windows 10
       #1

    excel conditional formating


    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
      My Computer

  2. Try3's Avatar
    Posts : 9,212
    Windows 10 Home x64 Version 21H2 Build 19044.1237
       #2

    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
      My Computer


  3. Posts : 166
    microsoft windows 10
    Thread Starter
       #3

    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
      My Computer


 

  Related Discussions
Our Sites
Site Links
About Us
Windows 10 Forums is an independent web site and has not been authorized, sponsored, or otherwise approved by Microsoft Corporation. "Windows 10" and related materials are trademarks of Microsoft Corp.

Designer Media Ltd
All times are GMT -5. The time now is 14:23.
Find Us




Windows 10 Forums