+ Reply to Thread
Results 1 to 18 of 18

Conditional Formatting cells in a large grid....

  1. #1
    Forum Contributor
    Join Date
    11-26-2008
    Location
    UK
    MS-Off Ver
    Using Excel 2019
    Posts
    303

    Conditional Formatting cells in a large grid....

    Hi

    I have the attached spreadsheet which makes the question easier to understand. Basically when you read across and down rows and columns, I'd like the intersecting cell to turn red if the horizontal and vertical row columns letters are different. So in the attached B5 is red because B1=A and A5 is B. Where the intersecting cells match in values, the cells will stay white.

    I can create a formula to conditionally format one cell to red: =A2<>B1

    but i have a large grid and it would mean re-creating each formula for each cell. Any idea on how to expand it to the whole range?
    Attached Files Attached Files
    Last edited by Barking_Mad; 06-26-2013 at 10:46 AM.

  2. #2
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Conditional Formatting cells in a large grid....

    Try

    =$A2<>B$1
    Attached Files Attached Files
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  3. #3
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Conditional Formatting cells in a large grid....

    cf is formula option of =$A2<>B$1
    applied to b2 and copied to rest of cells
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  4. #4
    Forum Contributor
    Join Date
    11-26-2008
    Location
    UK
    MS-Off Ver
    Using Excel 2019
    Posts
    303

    Re: Conditional Formatting cells in a large grid....

    Edit: How do I stop it from turning the cells a colour if only one of them has a value in it?
    Last edited by Barking_Mad; 06-25-2013 at 08:21 AM. Reason: put the quesiton back in so the answer below makes sense.

  5. #5
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Conditional Formatting cells in a large grid....

    Perhaps..

    =AND($A2<>B$1,$A2<>"")

  6. #6
    Forum Contributor
    Join Date
    11-26-2008
    Location
    UK
    MS-Off Ver
    Using Excel 2019
    Posts
    303

    Re: Conditional Formatting cells in a large grid....

    Quote Originally Posted by Fotis1991 View Post
    Perhaps..

    =AND($A2<>B$1,$A2<>"")
    Thanks that works a treat. Cant get my head round equations. When someone types them they makes sense, when i try to make them, it all goes pear shaped!

  7. #7
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Conditional Formatting cells in a large grid....

    ........................

  8. #8
    Forum Contributor
    Join Date
    11-26-2008
    Location
    UK
    MS-Off Ver
    Using Excel 2019
    Posts
    303

    Re: Conditional Formatting cells in a large grid....

    Ah, just noticed one more thing. I have another equation which before the conditional formatting counted the number of times "n" was in a cell. Can:

    =AND($A2<>B$1,$A2<>"")

    be adjusted to it also places "n" inside a cell that is turned the colour of the conditional formatting?

  9. #9
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Conditional Formatting cells in a large grid....

    An easy way is to put a separate condition for this...

    =b2="n"

  10. #10
    Forum Contributor
    Join Date
    11-26-2008
    Location
    UK
    MS-Off Ver
    Using Excel 2019
    Posts
    303

    Re: Conditional Formatting cells in a large grid....

    I can't get this working as a conditional format, but the below obviously wont work. If one cell is more or less than another insert "n", otherwise leave blank.

    =IF(A1<>B1,"n","")

    Then format the cell with the chosen colour. What am i doing wrong?
    Last edited by Barking_Mad; 06-26-2013 at 06:13 AM.

  11. #11
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Conditional Formatting cells in a large grid....

    See what i mean
    Attached Files Attached Files

  12. #12
    Forum Contributor
    Join Date
    11-26-2008
    Location
    UK
    MS-Off Ver
    Using Excel 2019
    Posts
    303

    Re: Conditional Formatting cells in a large grid....

    Hi,

    Thanks again for the swift reply

    I don't think im explaining myself very well. You kindly sorted it out so that the cells turned green in your example sheet. Perfect. They also need to have "n" inserted at the same time they turn green. So all those cells in the example sheet that are green, would also have the letter "n" in them. This is so I can do a calculation based on the number of times "n" features in the grid.

    And in case you're wondering! The purposes of it turning green automatically was so that the user wouldn't have to enter "n" every time the cells weren't identical - as it's a large sheet and quite time consuming...

    Thanks and apologies

  13. #13
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Conditional Formatting cells in a large grid....

    Ok. Let's try in this way.
    Attached Files Attached Files

  14. #14
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Conditional Formatting cells in a large grid....

    you cant insert text into a cell with cf,
    as long as you dont type into those cells you can just put the same formula as the cf in the cell with an IF
    if(AND($A2<>B$1,$A2<>""),"n","")
    Attached Files Attached Files

  15. #15
    Forum Contributor
    Join Date
    11-26-2008
    Location
    UK
    MS-Off Ver
    Using Excel 2019
    Posts
    303

    Re: Conditional Formatting cells in a large grid....

    bugger hmmmm..........

  16. #16
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Conditional Formatting cells in a large grid....

    Quote Originally Posted by Barking_Mad View Post
    bugger hmmmm..........
    This means that no one of our 2 suggestions works for you?

    Perhaps it's time to try to be more clear about the expected results? As you see both of us try to help you and i believe that both of us have the knowledge to do this..

  17. #17
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Conditional Formatting cells in a large grid....

    If my understanding is correct, this should do what you want.
    Attached Files Attached Files
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  18. #18
    Forum Contributor
    Join Date
    11-26-2008
    Location
    UK
    MS-Off Ver
    Using Excel 2019
    Posts
    303

    Re: Conditional Formatting cells in a large grid....

    Hi,

    THanks for your help, I didnt want a formula in unfortunately, as I have other values which would be potentially messed up if they were deleted. I went on the macro thread and got a solution. NOt perfect but it works well

    Thanks all for your time, much appreciated.

    http://www.excelforum.com/excel-prog...in-a-grid.html

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1