none
Highlight cells when condition meets RRS feed

  • Question

  • Hi All,

    I need either a formula or macro to highlight cells in below scenario

    My Excel sheet has “Date & time” in “Column A “, and “Case ID & Batch ID” in “Column B & C”, I need the cell in the same row in “column H” should get highlights when the current “Date & time” exceeds 8 hours from the date & time in column A only when the cell in “Column D” has specific text.

    When I entered the word “Closed” in cell in same row in “Column E” the highlight should be removed.

    Also the case ID in column B & C has may have many duplicate entries and I need only the row in where the first entry entered at the first time should get highlighted and the duplicate entries can be ignored.

    Column A

    Column B

    Column C

    Column D

    Column E

    Column F

    Column G

    Column H

    Date & Time

    Case ID

    Batch ID

    Due 

     

     

     

    Status

    12/5/2019 1:09

    125486

    6

    TAT

     

     

     

     

    12/6/2019 2:23

    125486

    6

    TAT

     

     

     

     

    12/6/2019 2:23

    125486

    6

    TAT

     

     

     

     

    12/6/2019 4:15

    258898

    2

    O/N

     

     

     

     

    12/6/2019 5:17

    58725

    13

    TAT

     

     

     

     

    12/6/2019 8:52

    58725

    13

    TAT

     

     

     

     

    12/6/2019 9:46

    628714

    27

    TAT

     Closed

     

     

     

    12/6/2019 11:31

    579872

    11

    O/N

     

     

     

     

    Any help would be highly appreciated

    Regards

    John


    Saturday, December 7, 2019 8:09 PM

Answers

    • Select H2:H9 (or however far down the data go).
    • H2 should be the active cell within the selection.
    • On the Home tab of the ribbon, select Conditional Formatting > New Rule...
    • Select 'Use a formula to determine which cells to format'.
    • Enter the formula

    =AND(COUNTIFS(B$2:B2,B2,C$2:C2,C2)=1,E2<>"Closed",NOW()-A2>TIME(8,0,0))

    • Click Format...
    • Activate the Fill tab.
    • Select red.
    • Click OK, then click OK again.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)


    Saturday, December 7, 2019 9:13 PM
  • With the added condition on column E:

    =AND($A2<>"",$A2=MAX(IF(($A$2:$A$100<NOW()-TIME(8,0,0))*($B$2:$B$100=$B2)*($C$2:$C$100=$C2)*($D$2:$D$100<>"O/N"),$A$2:$A$100)),$E2<>"Closed")


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Johnmiller1234 Tuesday, December 10, 2019 10:40 PM
    Tuesday, December 10, 2019 10:27 PM

All replies

    • Select H2:H9 (or however far down the data go).
    • H2 should be the active cell within the selection.
    • On the Home tab of the ribbon, select Conditional Formatting > New Rule...
    • Select 'Use a formula to determine which cells to format'.
    • Enter the formula

    =AND(COUNTIFS(B$2:B2,B2,C$2:C2,C2)=1,E2<>"Closed",NOW()-A2>TIME(8,0,0))

    • Click Format...
    • Activate the Fill tab.
    • Select red.
    • Click OK, then click OK again.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)


    Saturday, December 7, 2019 9:13 PM
  • Hello Hans Vogelaar,

    Thank you for the workaround, all work good and below the result.

    Requires one modification, as i requested earlier, I need the cell in the same row in “column H” should get highlights when the current “Date & time” exceeds 8 hours from the date & time in column A only when the cell in “Column D” has specific text.

    The cells in Column H, should be be highlighted only when the cell in "Column D" has text "TAT" and no need to be highlighted when the cell in "Collumn D" has text "O/N"


    Column A

    Column B

    Column C

    Column D

    Column E

    Column F

    Column G

    Column H

    Date & Time

    Case ID

    Batch ID

    Due 

     

     

     

    Status

    12/5/2019 1:09

    125486

    6

    TAT

     

     

     

     

    12/6/2019 2:23

    125486

    6

    TAT

     

     

     

     

    12/6/2019 2:23

    125486

    6

    TAT

     

     

     

     

    12/6/2019 4:15

    258898

    2

    O/N

     

     

     

     

    12/6/2019 5:17

    58725

    13

    TAT

     

     

     

     

    12/6/2019 8:52

    58725

    13

    TAT

     

     

     

     

    12/6/2019 9:46

    628714

    27

    TAT

     

     

     

     

    12/6/2019 11:31

    579872

    11

    O/N

     

     

     

     

    Regards

    John

    Sunday, December 8, 2019 2:07 AM
  • Hi Hans Vogelaar,

    I have changed it as below and works good.

    =AND(COUNTIFS(B$2:B2,B2,C$2:C2,C2)=1,E2<>"Closed",D2<>"O/N",NOW()-A2>TIME(8,0,0))

    Thank you very much for your help on this

    Regards

    John

    Sunday, December 8, 2019 7:28 AM
  • Hi Hans Vogelaar,

    Now checking for the possibility to highlight only when the latest “Date & Time” the “Case ID  & Batch ID” entered and ignored the entries for the old date & time.

    Example below

    Column A

    Column B

    Column C

    Column D

    Column E

    Column F

    Column G

    Column H

    Date & Time

    Case ID

    Batch ID

    Due 

     

     

     

    Status

    12/5/2019 1:09

    125486

    6

    TAT

     

     

     

     

    12/6/2019 2:23

    125486

    6

    TAT

     

     

     

     

    12/6/2019 2:27

    125486

    6

    TAT

     

     

     

     

    12/6/2019 4:15

    258898

    2

    TAT

     

     

     

     

    12/6/2019 5:17

    58725

    13

    TAT

     

     

     

     

    12/6/2019 8:52

    58725

    13

    TAT

     

     

     

     

    12/6/2019 9:46

    628714

    27

    TAT

     

     

     

     

    12/6/2019 11:31

    579872

    11

    TAT

     

     

     

     

    12/6/2019 12:37

    128486

    1

    O/N

     

     

     

     

    Regards

    John

    Monday, December 9, 2019 2:32 PM
  • Do you still need the condition that column D <> "O/N" and column E <> "Closed"?

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, December 9, 2019 3:05 PM
  • Yes sir, still the conditional format required.

    Regards

    John

    Monday, December 9, 2019 3:59 PM
  • Use the following formula for the conditional formatting rule:

    =$A2=MAXIFS($A$2:$A$100,$A$2:$A$100,"<"&NOW()-TIME(8,0,0),$B$2:$B$100,$B2,$C$2:$C$100,$C2,$D$2:$D$100,"<>O/N")

    Adjust the ranges as needed.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, December 9, 2019 6:00 PM
  • Hi Hans Vogelarr,

    There is no changing in color in the cells after using the formula in column H

    Regards

    John


    Tuesday, December 10, 2019 2:00 AM
  • Could you create a stripped-down copy of the workbook (without sensitive information) and make it available through one of the websites that let you upload and share a file, such as OneDrive, Google Drive, FileDropper or DropBox. Then post a link to the uploaded and shared file here.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, December 10, 2019 12:05 PM
  • Hi Hans Vogelaar,

    Regret for the delay in reply

    I have manually make the format bold for the "latest date & time" cells for your internal reference.

    https://spaces.hightail.com/space/QTJuPjTDWl/files/fi-807a1181-19db-4953-ac72-817f8186f040/fv-6a8a30d2-162a-489d-b95e-862e8abd1821/Sample%20for%20Conditional%20highlight.xlsx

    Please let me know if any further help required

    Regards

    John

    Tuesday, December 10, 2019 6:08 PM
  • Uhh - your sample workbook does not contain any conditional formatting rule.

    The formula that I posted works right out of the box:

    See https://www.dropbox.com/s/bh0nz0ux24ticwg/Sample%20for%20Conditional%20highlight.xlsx?dl=1


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, December 10, 2019 8:00 PM
  • Hi Hans Vogelaar,

    Thank you but I don't know why the format is still not appears correctly when I download and open the same work book in my system.

    https://spaces.hightail.com/space/QTJuPjTDWl/files/fi-6e0ae042-6537-4eb2-b165-ade6a769cff1/fv-084188fa-429e-4e90-8a6f-984c88e65819/not%20highlighted.xlsx

    https://spaces.hightail.com/space/QTJuPjTDWl/files/fi-e150412f-f42d-4fe0-a693-512cd10cd9a5/fv-d1a33864-4057-445b-a044-6a3b3cb4056d/not%20highlight.PNG

    I am using Office 2007

    Regards

    John



    Tuesday, December 10, 2019 9:07 PM
  • Ah - the MAXIFS function is not available in Excel 2007. Use this formula instead:

    =$A2=MAX(IF(($A$2:$A$100<NOW()-TIME(8,0,0))*($B$2:$B$100=$B2)*($C$2:$C$100=$C2)*($D$2:$D$100<>"O/N"),$A$2:$A$100))

    I have updated the sample workbook at https://www.dropbox.com/s/bh0nz0ux24ticwg/Sample%20for%20Conditional%20highlight.xlsx?dl=1


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, December 10, 2019 9:39 PM
  • Hi this works perfect with one single change required.

    The blank cells also get highlighted, is there any chance to get this resolved

    https://spaces.hightail.com/space/QTJuPjTDWl/files/fi-e8626d88-6b70-4a06-bcca-0733deb6aee6/fv-7b16b943-45e3-4591-b86c-a491b49b7c9a/Blanks%20get%20highlighted.xlsx

    And as requested earlier the highlights should be removed when entered the word "closed" in "column E".

    I my self modify the formula to add the requirement for column E, however when I entered the word "closed" the highlight moved to the previous date & time from the latest date & time.

    Regards

    John




    Tuesday, December 10, 2019 10:12 PM
  • Change the formula to

    =AND($A2<>"",$A2=MAX(IF(($A$2:$A$100<NOW()-TIME(8,0,0))*($B$2:$B$100=$B2)*($C$2:$C$100=$C2)*($D$2:$D$100<>"O/N"),$A$2:$A$100)))

    Remark: you now have multiple conditional formatting rules. Remove all except the one listed at the top in the Conditional Formatting > Manage Rules dialog.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, December 10, 2019 10:20 PM
  • With the added condition on column E:

    =AND($A2<>"",$A2=MAX(IF(($A$2:$A$100<NOW()-TIME(8,0,0))*($B$2:$B$100=$B2)*($C$2:$C$100=$C2)*($D$2:$D$100<>"O/N"),$A$2:$A$100)),$E2<>"Closed")


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Johnmiller1234 Tuesday, December 10, 2019 10:40 PM
    Tuesday, December 10, 2019 10:27 PM
  • Hi Hans Vogelaar,

    This is Awesome, thanks for getting this done. You aer a lifesaver.

    Regards

    John

    Tuesday, December 10, 2019 10:40 PM