+ Reply to Thread
Results 1 to 9 of 9

Conditional Format Cell Color based on Two Other Cell Values Without Blank Cells Affecting

  1. #1
    Registered User
    Join Date
    06-20-2015
    Location
    Mexico
    MS-Off Ver
    2013
    Posts
    6

    Conditional Format Cell Color based on Two Other Cell Values Without Blank Cells Affecting

    I have a maintenance task sheet based on hours in one column and days in another.
    Each row contains the task followed by the hours and days due in their respective columns.

    Some task are based on both hours and days due.
    Some task are based on only hours due.
    Some task are based on only days due.

    I wish to change the font color of the TASK title in each row depending on the hours and days remaining. i.e.

    If the Hours OR Days Due where less than or equal to 20 and 7 respectively color RED
    If both Hours AND Days Due where more than 20 and 7 respectively color GREEN.
    The color of the TASK based only on Hours or Days must NOT be affected by a Blank Cell

    i.e.
    A B C
    TASK Hours Due Days Due
    Oil Change 20 7 RED
    Filter Change 21 8 GREEN
    Brake Check 100 6 RED
    Water Level 5 RED
    Air Pressure 35 GREEN

    So far I have conditional formatted the first column as below:
    =AND(B2>20, C2>7). for the GREEN
    =OR(B2<=20, C2<=7). for the RED

    but get stuck on the NOT(ISBLANK).
    As soon as I have either the Hours or Days Due cell blank the TASK goes RED.

    Your kind input is greatly appreciated.

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,445

    Re: Conditional Format Cell Color based on Two Other Cell Values Without Blank Cells Affec

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic


    BTW indicating " home" as location does not help

  3. #3
    Registered User
    Join Date
    06-20-2015
    Location
    Mexico
    MS-Off Ver
    2013
    Posts
    6

    Re: Conditional Format Cell Color based on Two Other Cell Values Without Blank Cells Affec

    Ok, Please find attached file.

    Below is the latest help I have received but does not allow an empty cell.

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    I look forward to any assistance
    Attached Files Attached Files

  4. #4
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,445

    Re: Conditional Format Cell Color based on Two Other Cell Values Without Blank Cells Affec

    I don't see what's wrong with your initial conditions ?
    =AND(B2>20, C2>7). for the GREEN none of the cells can be blank
    =OR(B2<=20, C2<=7). for the RED allows for a blank cell
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    06-20-2015
    Location
    Mexico
    MS-Off Ver
    2013
    Posts
    6

    Re: Conditional Format Cell Color based on Two Other Cell Values Without Blank Cells Affec

    Many Thanks.

    I have cell C5 empty because It does not have Water Level checked based on Hours Due, only Days Due. This means the cell is left blank; so Water Level should be Red. On my spreadsheet it is Black.

    Blank Cells should not cause a color change.

    I hope that clarifys my problem?

  6. #6
    Registered User
    Join Date
    06-20-2015
    Location
    Mexico
    MS-Off Ver
    2013
    Posts
    6

    Re: Conditional Format Cell Color based on Two Other Cell Values Without Blank Cells Affec

    I have also noticed that if I put a dash "-" in the empty cells for example it works properly but I don't want to have to put anything in the empty cells.

  7. #7
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,445

    Re: Conditional Format Cell Color based on Two Other Cell Values Without Blank Cells Affec

    Quote Originally Posted by mexmike View Post
    Many Thanks.

    I have cell C5 empty because It does not have Water Level checked based on Hours Due, only Days Due. This means the cell is left blank; so Water Level should be Red. On my spreadsheet it is Black.

    Blank Cells should not cause a color change.

    I hope that clarifys my problem?
    The quotes I bolded are contradictory. Have you looked at my file?

  8. #8
    Registered User
    Join Date
    06-20-2015
    Location
    Mexico
    MS-Off Ver
    2013
    Posts
    6

    Re: Conditional Format Cell Color based on Two Other Cell Values Without Blank Cells Affec

    Yes, I have Thanks.

    I have re-uploaded the file with changes to values in order to clarify my problem.

    As you'll see, cells that are blank affect the color when they shouldn't; but cells with something in them other than numbers allow the sheet to work as desired.

    I really don't want to have to put "XXX" or anything in the cells to get the correct color.

    Thanks again for you patience
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    06-20-2015
    Location
    Mexico
    MS-Off Ver
    2013
    Posts
    6

    Re: Conditional Format Cell Color based on Two Other Cell Values Without Blank Cells Affec

    Hi,
    I am still having problems with conditional formatting. Conditional Formatting using the built in Excel formatting seems to require very long formulas for what I wish to achieve. I was hoping to get some help coding with VBA instead.
    I have attached a more exact example of my problem without adding the Excel Conditional Formatting. I have not been able to include the ISBLANK or NOT(ISBLANK) in all conditions due to lack of skill. I would appreciate any help arriving at a VBA solution. The columns with "TRUE" or "FALSE", only represent the formulas that I would use for the formatting. In reality they would be TASK's i.e. "Replace Oil Filter"

+ 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 Format of cells based on other cell values
    By hamidxa in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 05-18-2015, 01:02 PM
  2. Replies: 5
    Last Post: 10-24-2013, 05:34 PM
  3. Conditional format to change color of row based on data in cell
    By PJenkins in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-02-2013, 09:39 AM
  4. Conditional Values affecting another Cell
    By dlenoxx in forum Excel General
    Replies: 2
    Last Post: 03-06-2009, 03:59 PM
  5. Format cell color based on multiple cell values
    By Zenaida in forum Excel General
    Replies: 3
    Last Post: 05-09-2006, 06:56 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