+ Reply to Thread
Results 1 to 4 of 4

Format blank cells based on another cell's value?

  1. #1
    Registered User
    Join Date
    08-25-2009
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    38

    Format blank cells based on another cell's value?

    Hello,

    Is it possible to format *blank* cells based on another cell's value?

    I know how to format cells based on their blank-ness. And I know how to format non-blank cells by comparing their values to another cell.

    But what if there's nothing in the blank cells to compare to the target cell?

    Any help would be appreciated.

    Thanks



    FURTHER DETAIL:
    I have a worksheet which dynamically generates a categorized list based on references from another sheet. THE NUMBER OF CATEGORIES AND ITEMS WITHIN EACH CATEGORY will change regularly, based on another sheet's data. In other words, which rows are "Category Heading" rows will vary dynamically, and cannot have static formatting.

    COLUMN A = Hidden "helper" column. Determines whether this row is a Category Heading or an item row, based on formula (counting items/categories on separate sheet).
    COLUMNS B & C = Display Category / Item names depending on the value of Column A
    COLUMNS D...F (and further) = BLANK for manual data entry. (Other cells will reference these for summing, averaging, etc.) I would like these blacked out if A="Cat."


    A B C D E F
    Cat CATEGORY 1 BLANK - WOULD LIKE BLACKED OUT WHEN COL A="Cat" (Black if A="Cat") (Black if A="Cat") (Black if A="Cat")
    Item Item 1 Data Data Data
    Item Item 2 Data Data Data
    Item Item 3 Data Data Data
    Cat CATEGORY 1 BLANK - WOULD LIKE BLACKED OUT BASED ON COL A="Cat" (Black if A="Cat") (Black if A="Cat") (Black if A="Cat")
    Item Item 1 Data Data Data
    Item Item 2 Data Data Data








    Thanks

  2. #2
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,405

    Re: Format blank cells based on another cell's value?

    Select your range (C1:F7 in the table you have above - you'll probably want something like C1:F100 or C1:Z100, etc)
    Click 'Conditional Formatting' then 'New Rule' then 'Use a formula to determine which cells to format'.
    In the formula box, enter:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    then choose black fill from the 'Format' box.

    That should do what you want.
    Regards,
    Aardigspook

    I recently started a new job so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
    If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
    If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
    You don't need to give me rep if I helped, but a thank-you is nice.

  3. #3
    Registered User
    Join Date
    08-25-2009
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    38

    Re: Format blank cells based on another cell's value?

    Thanks! That worked great -- I thought I had tried that but I must have messed up the syntax somehow.

  4. #4
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,405

    Re: Format blank cells based on another cell's value?

    You're welcome and thanks for the feedback (and the rep).

    If that takes care of your original question, then please take a moment to mark the thread as Solved so others know there's an answer here (instructions are in my sig). Thanks.

+ 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. Changing Cell format based on another cells format.
    By MrStevie in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-05-2017, 03:02 PM
  2. Replies: 8
    Last Post: 06-27-2015, 03:21 PM
  3. Replies: 4
    Last Post: 09-19-2014, 08:10 AM
  4. Replies: 5
    Last Post: 05-04-2011, 05:01 PM
  5. Replies: 1
    Last Post: 05-03-2011, 05:46 PM
  6. how do i format a cell based on format of a range of cells?
    By Chris Hardick in forum Excel General
    Replies: 2
    Last Post: 04-03-2006, 03:55 AM
  7. [SOLVED] format row based on non-blank or blank cell
    By mhoffmeier in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-31-2005, 04:05 AM

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