+ Reply to Thread
Results 1 to 5 of 5

Applying Conditional Formatting Based On Cell Value, Combining Two AND Conditions

  1. #1
    Registered User
    Join Date
    08-04-2010
    Location
    United States
    MS-Off Ver
    Office 365 for windows
    Posts
    62

    Unhappy Applying Conditional Formatting Based On Cell Value, Combining Two AND Conditions

    Hello. I have been working at this for a bit and I can't seem to take any more steps. I feel like it should be really simple.

    I finally have this conditional formatting to work the way I want but I need to apply it based on the value of a cell.

    Basically I have 2 cells with data validation, one where I choose the month number 1 through 12, and another where I choose a day range "1-15" or "16-31".

    The intended result is that the rows for which the conditional formatting rule applies will highlight text in red font when I select the month and day range.

    Currently my rule looks like this which works when I select the cell which has my number for the month and 1-15 to highlight those days.

    =OR(AND(MONTH($T3)=$Z$13,$Z$14="1-15",DAY($T3)<16),AND(MONTH($T3)=$Z$13,$Z$14="16-31",DAY($T3>15)))

    Of course because of the OR, it doesn't work when I select "16-31" for the date range. Multiple tries with an IF condition haven't worked. Help would be very much appreciated.
    Attached Files Attached Files
    Last edited by lOYvEpi6M87nEoIF0ul8; 12-01-2022 at 10:40 PM. Reason: Upload File and correct formula

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Applying Conditional Formatting Based On Cell Value, Combining Two AND Conditions

    =OR(AND(MONTH($T3)=$Z$13,$Z$14="1-15",DAY($T3)<16),AND(MONTH($T4)=$Z$13,$Z$14="16-31",DAY($T3>15)))

    Why are you checking T3 for the month in the first case, and T4 in the second case? From your description I would expect them to be the same.

    Also, in the future, attaching your actual Excel file will allow us to fix things much faster than a picture.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    08-04-2010
    Location
    United States
    MS-Off Ver
    Office 365 for windows
    Posts
    62

    Re: Applying Conditional Formatting Based On Cell Value, Combining Two AND Conditions

    That was an oversight. Thank you for bringing it to my attention. I didn't provide a file because It was sort of a nightmare to prepare the file from the original. I did upload it though.

  4. #4
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Applying Conditional Formatting Based On Cell Value, Combining Two AND Conditions

    You had a parenthesis in the wrong place

    original
    =OR(AND(MONTH($T3)=$Z$13,$Z$14="1-15",DAY($T3)<16),AND(MONTH($T3)=$Z$13,$Z$14="16-31",DAY($T3>15)))

    correction
    =OR(AND(MONTH($T3)=$Z$13,$Z$14="1-15",DAY($T3)<16),AND(MONTH($T3)=$Z$13,$Z$14="16-31",DAY($T3)>15))

    Having the file made this go a lot faster, thanks.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    08-04-2010
    Location
    United States
    MS-Off Ver
    Office 365 for windows
    Posts
    62

    Re: Applying Conditional Formatting Based On Cell Value, Combining Two AND Conditions

    You're welcome. I was trying to avoid the nightmare of getting it scrubbed etc.
    Thank you very much. I'm still an excel newbie trying to figure things out. Guess I was looking at it way too long. Thanks again.

+ 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] Using conditional formatting to fill a cell colour based upon 1 of 2 conditions
    By oneeasygeezer in forum Outlook Formatting & Functions
    Replies: 3
    Last Post: 09-05-2018, 02:39 PM
  2. Replies: 4
    Last Post: 01-21-2017, 02:57 PM
  3. [SOLVED] Conditional Formatting - Combining 2 conditions
    By shaunguyver in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 04-16-2015, 09:08 AM
  4. Conditional formatting - more than 3 conditions - result based on another cell
    By Mostlygood in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-07-2013, 10:07 AM
  5. Applying Conditional formatting for entire Row based on date
    By acsishere in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-14-2009, 04:07 PM
  6. Conditional Formatting 4+ conditions, format rows based on cell
    By nockam in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 07-12-2006, 06:12 PM
  7. [SOLVED] Applying conditional formatting to cell based on another cell's in
    By kdesemple in forum Excel General
    Replies: 1
    Last Post: 03-22-2006, 02:40 PM

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