+ Reply to Thread
Results 1 to 10 of 10

Conditional Formatting Using a named range

  1. #1
    Forum Contributor
    Join Date
    06-16-2014
    Location
    United States
    MS-Off Ver
    Home 2010
    Posts
    122

    Conditional Formatting Using a named range

    I have looked at other posts on this topic and they have not helped.

    I have a sheet called Quote Summary and a sheet called AssemblyBoms.
    In the Bom sheet, I have a named range for QtyPer and a named range for unit price because these ranges are dynamic.
    On the quote summary sheet, I want to format the cell (A9 and eventually A9 to A28 and so on), which contains a formula right now that only references a tag and does some equivalence checking (=IF(AND(QSA>0,QSA=qty1a),assembly1,"")), to have a yellow background if QtyPer>0 but unit price = "" or 0. I know the formulas for this, but it is not formatting anything. Attached workbook below.

    QUOTETEMPLATEMACROTEST.xlsm

    Conditional Formula for true:
    ="AND(qtyper1 > 0, unita1 = "")"

    I typed it in without the = or "", but the formatting added those in for me.

    I typed a number in the qtyper1 range and left the unita1 range blank to test it and no formatting has occurred.

    Am I missing something?

  2. #2
    Forum Contributor
    Join Date
    06-16-2014
    Location
    United States
    MS-Off Ver
    Home 2010
    Posts
    122

    Re: Conditional Formatting Using a named range

    UPDATE: Tried referencing the individual cell that I was testing and still no formatting.

    Formula used:

    ="AND('ASSEMBLYBOMS'!E2 > 0, 'ASSEMBLYBOMS'!G2 = "")"

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,050

    Re: Conditional Formatting Using a named range

    Remove the " at the begining and end
    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
    Forum Contributor
    Join Date
    06-16-2014
    Location
    United States
    MS-Off Ver
    Home 2010
    Posts
    122

    Re: Conditional Formatting Using a named range

    Still no formatting

  5. #5
    Forum Contributor
    Join Date
    06-16-2014
    Location
    United States
    MS-Off Ver
    Home 2010
    Posts
    122

    Re: Conditional Formatting Using a named range

    REVISE: It did work for the individual cell reference, but not the named ranges. I need to look at the whole ranges.

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,050

    Re: Conditional Formatting Using a named range

    wow why do you have all those named ranges??

  7. #7
    Forum Contributor
    Join Date
    06-16-2014
    Location
    United States
    MS-Off Ver
    Home 2010
    Posts
    122

    Re: Conditional Formatting Using a named range

    Because cell references, whether absolute or relative, are not foolproof. I do need to clean some of those up that aren't being used. Some of them are from an old excel made by someone else that I don't use in the new excel.

    On the topic at hand, I found that using helper columns, well, helped :P I wanted to avoid using them but it looks like I have to. In my BOM sheet, I have this formula in column X: =AND($E2>0,$G2=""). Then, in column Y, I have the boolean text converted to boolean standard values: =IF($X2,1,0). I sum this column in Y62:=SUM($Y2:$Y61). Basically it counts the errors specified in my first post. Then, my conditional formatting formula in the summary sheet is: =ASSEMBLYBOMS!$Y62>0. It works alright.

    More work than I wanted to waste space on in a workbook, but I guess it can't be helped. I'm open to any shortcuts.

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,050

    Re: Conditional Formatting Using a named range

    I am out of time right now, but should be able to take another look in an hour or so. can you perhaps clean up some of those names ranges that you are not using and post the file again?

  9. #9
    Forum Contributor
    Join Date
    06-16-2014
    Location
    United States
    MS-Off Ver
    Home 2010
    Posts
    122

    Re: Conditional Formatting Using a named range

    I will try, hehe. Gotta make sure theyre not being used or referenced etc. After I am done there will still be quite a few considering we have space for 20 assemblies on the BOM and some much needed info from all 20 to put in other sheets.

    Doing some formatting and testing for my previously mentioned method involving helper columns.

    The macros in the workbook are pretty much all formatting for other functions of the workbook, so don't worry about them.

  10. #10
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,050

    Re: Conditional Formatting Using a named range

    When you use this formula in a cell on its own...
    =AND(qtyper1 > 0, unita1 = "")
    it returns FALSE, meaning the CF will not trigger. When you break that down into its component parts...
    =qtyper1 = 0
    =unita1 = 0

  11. #11
    Forum Contributor
    Join Date
    06-16-2014
    Location
    United States
    MS-Off Ver
    Home 2010
    Posts
    122

    Re: Conditional Formatting Using a named range

    Heres my workbook using hidden helper columns to do conditional formatting. If there's a part with a qty per and no unit price (a part was missed in the pricing process), turn the assembly number red on the summary sheet.

    QUOTETEMPLATEMACROTEST.xlsm

+ 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. Replies: 4
    Last Post: 02-17-2014, 10:48 PM
  2. Conditional Formatting using Named Range
    By pcm1969 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-25-2013, 01:13 PM
  3. [SOLVED] Conditional formatting if value in cell is found in a named range
    By Grumpy Grandpa in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-15-2006, 11:35 AM
  4. Named range using conditional formatting
    By derekvho@gmail.com in forum Excel General
    Replies: 13
    Last Post: 04-10-2006, 06:10 PM
  5. [SOLVED] named range, conditional formatting
    By drabbacs in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-17-2005, 02:06 PM

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