+ Reply to Thread
Results 1 to 4 of 4

Conditional formatting - fill range if all cells in range are not blank

  1. #1
    Registered User
    Join Date
    02-19-2021
    Location
    Brno, Czech republic
    MS-Off Ver
    365 ProPlus
    Posts
    2

    Question Conditional formatting - fill range if all cells in range are not blank

    Hi,

    I have a table with 3 evaluation scores per agent. It looks like this (see attached):
    A1 - name of the agent
    A2 - score 1; A3 - score 2; A4 - score 3

    B1 - name of the agent
    B2 - score 1; etc.

    I would like to set a conditional formatting to highlight A2:A4 if all 3 cells in this range have a score (a.k.a. are not blank). If one or more of these cells is empty (no score yet), the range will not highlight.

    I tried different formulas that I found online, but they didn't quite work:
    =COUNTA(A2:A4)=3
    =COUNTBLANK(A2:A4)=0
    =SUMPRODUCT(--(A2:A4<>""))=3
    - all 3 only filled A2 but not A3 and A4, although the cond. formatting should apply for =$A$2:$A$4

    Apparently I am doing something wrong, but I just can't figure out what

    Do you perhaps have any idea what I'm doing wrong?

    Thanks a lot!

    BTW Hello, Im new here on this forum, so hope I don't make too many mistakes. If so, please do let me know. Thanks!

    Kathi
    Attached Files Attached Files
    Was forced to live on this planet, wanna leave!

  2. #2
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,032

    Re: Conditional formatting - fill range if all cells in range are not blank

    =a2&a3&a4 <> ""

    =CONCAT(A2:A4) <> ""
    Last edited by mehmetcik; 02-19-2021 at 02:09 PM.
    Please Avoid Joining My List Of Blocked Users by:

    Saying Please and Thank you.

    Making requests not demands.

    Checking back on your post. I will not edit any post after 4 days.

    Marking threads as closed once your issue is resolved. How? The tools at the top

    Any reputation (*) points appreciated. None of us gets paid here.

    If you found someone's input useful, please take a second to click the * at the bottom left to let them know

  3. #3
    Registered User
    Join Date
    02-19-2021
    Location
    Brno, Czech republic
    MS-Off Ver
    365 ProPlus
    Posts
    2

    Re: Conditional formatting - fill range if all cells in range are not blank

    Hi mehmetcik,

    thanks for the formulas, but somehow these trigger the fill even when all 3 cells are blank...

    However your formulas brought me to an idea that I found to be working....quite a long workaround I guess, but I take it for now :-D

    I got this working:
    =IF($A$2<>"","filled","nope")&(IF($A$3<>"","filled","nope"))&IF($A$4<>"","filled","nope")="filledfilledfilled"

    This however only works like intended when the references are absolute. When I change it to relative references or only "partial" absolute references ("$A2", "A$2", "A2") it only fills the first cell like the formulas I tried at the very beginning.

    I tried your formulas too with absolute references, but that ended up in the fill of all 3 cells alreadybeing triggered if only 1 of them was not blank.

    As I wrote, not exactly smooth and clear, but for now it works :-D

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,540

    Re: Conditional formatting - fill range if all cells in range are not blank

    Using the file attached to post #1, I applied the following rule to B2:H4
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    D2:D4 and G2:G4 highlighted.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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: 5
    Last Post: 01-01-2021, 05:39 PM
  2. Conditional Formatting a range of cells based on a range of cells on another sheet.
    By operationsahcc in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-13-2020, 05:47 AM
  3. [SOLVED] Conditional Formatting when a range of cells is blank.
    By Cidona in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 09-16-2020, 06:00 AM
  4. Conditional Formatting Range of Cells If they are Blank
    By schmidt1962 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-02-2013, 06:37 PM
  5. Replies: 3
    Last Post: 08-13-2013, 09:44 AM
  6. [SOLVED] Conditional formatting - colour fill entire row by date range
    By oOLILYOo in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-01-2012, 10:31 AM
  7. Conditional Formatting if 6 or more cells in a range are not blank
    By marquella in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-03-2011, 03:08 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