Highlight a cell if a different cell isn't blank

%3CLINGO-SUB%20id%3D%22lingo-sub-2170115%22%20slang%3D%22en-US%22%3EHighlight%20a%20cell%20if%20a%20different%20cell%20isn't%20blank%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2170115%22%20slang%3D%22en-US%22%3E%3CP%3E%3CU%3E%3CSTRONG%3EShort%20Explanation%3C%2FSTRONG%3E%3C%2FU%3E%3C%2FP%3E%3CP%3EI'm%20trying%20to%20highlight%20a%20cell%20based%20on%202%20conditions%3A%3C%2FP%3E%3CP%3E1.%20The%20cell%20contains%20an%20exact%20word%20(%22Scheduled%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20AND%3C%2FP%3E%3CP%3E2.%20A%20different%20cell%20in%20the%20same%20row%20isn't%20blank%20(contains%20any%20data).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CU%3E%3CSTRONG%3ELong%20Explanation%3C%2FSTRONG%3E%3C%2FU%3E%3C%2FP%3E%3CP%3EI'm%20tracking%20scheduled%20appointments%20for%20my%20clients%2C%20including%20the%20date%2C%20appointment%20status%2C%20and%20clock%20in%20and%20out%20times.%20In%20my%20attached%20example%2C%20Column%20C%20(the%20column%20that%20contains%20the%20cells%20I'm%20wanting%20to%20highlight)%20tracks%20the%20appointment%20status%20and%20can%20contain%20these%20possible%20entries%20(there%20is%20a%20data%20validation%20drop%20down%20list%20in%20the%20cell)%3A%20%22Scheduled%22%2C%20%22Complete%22%2C%20%22No%20Show%22.%20Column%20E%20contains%20the%20clock%20out%20times%20(%2212%3A00%20PM%22%2C%20%2211%3A30%20AM%22%2C%20etc).%20When%20a%20client%20completes%20an%20appointment%20and%20a%20clock%20out%20time%20is%20entered%20in%20Column%20E%2C%20my%20intention%20is%20to%20change%20Column%20C%20from%20%22Scheduled%22%20to%20%22Complete%22.%20I%20want%20to%20be%20alerted%20when%20I've%20entered%20a%20clock%20out%20time%20(the%20cell%20in%20Column%20E%20is%20no%20longer%20blank)%2C%20but%20I've%20forgotten%20to%20change%20Column%20C%20from%20%22Scheduled%22%20to%20%22Complete%22.%20I'd%20like%20to%20be%20alerted%20by%20seeing%20the%20cell%20that%20says%20%22Scheduled%22%20(but%20should%20say%20%22Complete%22)%20become%20highlighted.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20there%20is%20a%20way%20to%20automatically%20change%20the%20cell%20in%20Column%20C%20to%20%22Complete%22%20any%20time%20there%20is%20data%20entered%20in%20Column%20E%20that%20would%20work%20too%2C%20but%20simply%20highlighting%20the%20cell%20would%20be%20fine.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20was%20messing%20around%20with%20Conditional%20Formatting%20but%20couldn't%20figure%20out%20how%20to%20add%20a%20rule%20that%20would%20highlight%20the%20cell%20if%20there%20is%20data%20in%20a%20different%20cell.%20Any%20help%20would%20be%20greatly%20appreciated%2C%20thank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2170115%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2170223%22%20slang%3D%22en-US%22%3ERe%3A%20Highlight%20a%20cell%20if%20a%20different%20cell%20isn't%20blank%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2170223%22%20slang%3D%22en-US%22%3EI%20recommend%20this%20formula%20in%20the%20Status%20column%3A%3CBR%20%2F%3E%3DIFS(E3%2C%22Complete%22%2CNOW()%26gt%3BB3%2BD3%2C%22No%20Show%22%2CTRUE%2C%22Scheduled%22)%3CBR%20%2F%3Eas%20for%20conditional%20formatting%20you%20use%20custom%20formula%20and%20a%20formula%20like%20this%3A%3CBR%20%2F%3Eapplied%20to%20range%20C%3AC%3CBR%20%2F%3E%3D(C1%3D%22Scheduled%22)*(E1%26gt%3B0)%3C%2FLINGO-BODY%3E
New Contributor

Short Explanation

I'm trying to highlight a cell based on 2 conditions:

1. The cell contains an exact word ("Scheduled")

                                    AND

2. A different cell in the same row isn't blank (contains any data).

 

Long Explanation

I'm tracking scheduled appointments for my clients, including the date, appointment status, and clock in and out times. In my attached example, Column C (the column that contains the cells I'm wanting to highlight) tracks the appointment status and can contain these possible entries (there is a data validation drop down list in the cell): "Scheduled", "Complete", "No Show". Column E contains the clock out times ("12:00 PM", "11:30 AM", etc). When a client completes an appointment and a clock out time is entered in Column E, my intention is to change Column C from "Scheduled" to "Complete". I want to be alerted when I've entered a clock out time (the cell in Column E is no longer blank), but I've forgotten to change Column C from "Scheduled" to "Complete". I'd like to be alerted by seeing the cell that says "Scheduled" (but should say "Complete") become highlighted.

 

If there is a way to automatically change the cell in Column C to "Complete" any time there is data entered in Column E that would work too, but simply highlighting the cell would be fine.

 

I was messing around with Conditional Formatting but couldn't figure out how to add a rule that would highlight the cell if there is data in a different cell. Any help would be greatly appreciated, thank you!

1 Reply
I recommend this formula in the Status column:
=IFS(E3,"Complete",NOW()>B3+D3,"No Show",TRUE,"Scheduled")
as for conditional formatting you use custom formula and a formula like this:
applied to range C:C
=(C1="Scheduled")*(E1>0)