+ Reply to Thread
Results 1 to 13 of 13

Conditional cell formatting not working properly

  1. #1
    Valued Forum Contributor luv2glyd's Avatar
    Join Date
    07-13-2008
    Location
    Seattle, WA, US
    MS-Off Ver
    Excel 2010
    Posts
    675

    Question Conditional cell formatting not working properly

    I have the following condition for formatting a range of cells (turning them red): =AND(BE314="S",BS314=5), and the range of cells I'm trying to format is: =$BW$314:$CC$332

    If I write the condition using $ signs like this: =AND($BE$314="S",$BS$314=5), the formatting of $BW$314:$CC$332 works fine, but as soon as I remove the $ signs and write it like this =AND(BE314="S",BS314=5), only cell BW314 is formatted, and the rest of the range is not. I'd like to leave the $ signs off b/c I need to paste this formatting to multiple areas in the workbook, and have it be depended not on =AND($BE$314="S",$BS$314=5), but whatever row and column this condition ends up in when I copy and paste the range of cells I am formatting.

    So for example, if I copy $BW$314:$CC$332 that has been conditionally formatted and paste it 100 rows down to $BW$414:$CC$432, I'd like the condition to automatically be AND(BE414="S",BS414=5) without me having to go in and changing where that condition is located.

    Is this possible to do?
    You either quit or become really good at it. There are no other choices.

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,320

    Re: Conditional cell formatting not working properly

    We might need a sample workbook illustrating the problem. I cannot replicate the problem. I can use relative referencing without difficulty in conditional formatting.
    Attached Files Attached Files
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    49,700

    Re: Conditional cell formatting not working properly

    Are BE314 and BS314 specific single cells that need to be referenced, or are they part of a range that you are testing against?

    If single cells, then you need to keep the $, but if they are a range (in those columns), then perhaps try $BE314 and $BS314
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Valued Forum Contributor luv2glyd's Avatar
    Join Date
    07-13-2008
    Location
    Seattle, WA, US
    MS-Off Ver
    Excel 2010
    Posts
    675

    Re: Conditional cell formatting not working properly

    Please see my next post
    Last edited by luv2glyd; 01-05-2017 at 08:23 PM.

  5. #5
    Valued Forum Contributor luv2glyd's Avatar
    Join Date
    07-13-2008
    Location
    Seattle, WA, US
    MS-Off Ver
    Excel 2010
    Posts
    675

    Re: Conditional cell formatting not working properly

    Ok, thank you for taking a look at it. Here's an example of what I am trying to achieve in the sheet attached: when I copy the blue square located in D10:E14 to D20:E24, I'd like the condition that makes it blue be in A20 and B20 automatically without me having to select the new square and rewrite the condition from =AND($A$10=5,$B$10="S") to =AND($A$20=5,$B$20="S").
    Attached Files Attached Files

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    49,700

    Re: Conditional cell formatting not working properly

    OK so what do you want, the full fill or just the cell?

  7. #7
    Valued Forum Contributor luv2glyd's Avatar
    Join Date
    07-13-2008
    Location
    Seattle, WA, US
    MS-Off Ver
    Excel 2010
    Posts
    675

    Re: Conditional cell formatting not working properly

    I'd like the rectangle in cells D20:E24 to be fully filled blue if A20=5 and B20="S", but need the rectangle to have this conditional reference to A20 and B20 as soon as I copy it over from above.

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    49,700

    Re: Conditional cell formatting not working properly

    OK, you cant have your cake and eat it.
    If you lock the cells so that they always reference A10 and B10 for the 1st block, then when you copy, those are the references that copy over too.
    If the criteria would always be in the same relative position (stays in same row, 3 columns to the left) you could maybe use OFFSET()

  9. #9
    Valued Forum Contributor luv2glyd's Avatar
    Join Date
    07-13-2008
    Location
    Seattle, WA, US
    MS-Off Ver
    Excel 2010
    Posts
    675

    Re: Conditional cell formatting not working properly

    Too bad.... They are always in the same relative position. Could you give an example of how to use OFFSET() in this case?

  10. #10
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    49,700

    Re: Conditional cell formatting not working properly

    hmm was trying this, but it doesnt seem to "carry"...
    =AND(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN(),1,1)),0,-3)=5,OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN(),1,1)),0,-2)="S")

  11. #11
    Valued Forum Contributor luv2glyd's Avatar
    Join Date
    07-13-2008
    Location
    Seattle, WA, US
    MS-Off Ver
    Excel 2010
    Posts
    675

    Re: Conditional cell formatting not working properly

    OK, thanks for trying. Any other suggestions?

  12. #12
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,320

    Re: Conditional cell formatting not working properly

    I have a suggestion, but you are probably not going to like it. It seems to me that a spreadsheet works best when things are copied as rows or columns, but I, too, have run into troubles when trying to copy things in "blocks" like this. I tend to work in small enough "blocks" that I will build the block with relative references one cell at a time. So, apply a conditional formatting rule to D10 that is AND(A10=5,B10="S"), D11 has the same formula, and so on through the 10 cells in D10:E14. It was tedious to set up the block like this, but now the block can be copied around fairly easily.

    I would expect that Ford's suggestion to use the OFFSET() function would work -- it is just a matter of really thinking through the logic of "from the current cell, how many cells to the left and up do I need to go to find the upper left cells in the block". Which means the formula needs a way to know where in the block it is. I have not thought it through fully, but I will think on it and see what comes of it.

  13. #13
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    49,700

    Re: Conditional cell formatting not working properly

    MrShorty, as you can see, I was trying that. If you put that is a cell and copy down and across, it provides the required TRUE for the CF, but when you put in as a rule, it wont apply.
    A
    B
    C
    D
    E
    F
    G
    H
    9
    $D$10
    10
    5
    S
    TRUE
    TRUE
    TRUE
    11
    TRUE
    TRUE
    12
    TRUE
    TRUE
    13
    TRUE
    TRUE
    14
    TRUE
    TRUE

    Because I could not use the ADDRESS function, I just put the starting reference in G9 and referenced it, as if it was teh ADDRESS result
    G10=AND(OFFSET(INDIRECT($G$9),0,-3)=5,OFFSET(INDIRECT($G$9),0,-2)="S")
    copied down and across

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Conditional formatting for icon sets is not working properly
    By Exceltrouble in forum Excel General
    Replies: 2
    Last Post: 09-16-2016, 08:43 AM
  2. [SOLVED] Conditional Formatting not working properly
    By Hodge1013 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 10-23-2015, 07:42 PM
  3. [SOLVED] Conditional formatting formula cell reference not incrementing properly
    By killerthun in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-23-2014, 07:29 PM
  4. [SOLVED] Conditional Formatting Over a Large Range Not Working Properly
    By ExcelDavid in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-11-2014, 08:47 PM
  5. Conditional Formatting Formula Not Working Properly
    By Oscar Martin in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-23-2014, 08:59 AM
  6. Conditional formatting not working properly
    By mlucey01 in forum Excel General
    Replies: 4
    Last Post: 01-08-2013, 08:35 PM
  7. [SOLVED] Conditional Formatting using VBA - Code not working properly
    By Tejas.T in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-07-2012, 10:19 AM

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