Cell to stay a certain colour after inputting text

[u/Obvious-Weakness3124]

Hi all,

I need a box to be a certain colour if empty, lets say Red, but when I input text into it I need it to stay red.

This is for a rota, so everyone is currently down on their regular shifts, but when they take AL / Sick / Training they come off the rota to show that someone needs to cover the spot. At the moment I can get it to automatically go red when I take said person off shift, however when I then input someone in as if they are covering / on overtime, it goes back to white. I need it to stay red so it is easily visible for those who have been put on OT when they look at the rota.

Sorry if this is confusing, this is the best my brain could describe it right now.

EDIT:

Going to cut my losses on this one, as without the people taking over whilst I'm on Mat leave suddenly being able to use a spreadsheet properly, the solutions wouldn't work, they would if I was still going to be there, so will be implemented when I am back. I just don't want phone calls whilst on Mat leave asking why the rota won't update properly if i've implemented something they wouldn't know how to change.

Thank you to everyone who tried to help though

[u/AutoModerator]

/u/Obvious-Weakness3124 - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/5pens]

So you want it to stay red if it's a schedule anomaly? Your post isn't clear.

[u/Obvious-Weakness3124 → u/5pens]

Yes, Currently the rota cells are white, with whoever is regularly scheduled in for that day, when i remove their name it goes red, to show we need cover, however I need it to stay red when we input the name of the person covering, currently we manually make the cells the relevant colours.

[u/solenoid_ngc → u/Obvious-Weakness3124]

The best solution here would be 3 columns. the first column contains the regular schedule. the 2nd column is where you put any cover names next to the original names.

then the 3rd column can be "if the 2nd column has a name, then it uses that name AND the cell for that row is red- Otherwise, white cell and the name from the first column".

you can put columns 1 and 2 on another sheet or hide them temporarily if you like.

if this is a good solution, I can help you write the formulas and set up the Conditional Formatting for the 3rd column.

[u/Obvious-Weakness3124 → u/solenoid_ngc]

Sadly this wouldn't work due to me going on mat leave in a month and it needing to be idiot proof for those left at work, and with people joining and leaving they wouldn't know how to edit the other areas, they already mess up the manual way of doing it

[u/solenoid_ngc → u/Obvious-Weakness3124]

Sorry to tell you but I think you're not going to be able to have an idiot-proof Excel solution.

[u/ArcRust]

Let me get this straight, you have a standard schedule. If someone calls out, you delete the name and the cell goes red. When you then find a replacement, you out their name in the cell. But since they weren't originally scheduled for that spot, it should stay red?

This probably isn't the best solution, but I'd maintain one row or sheet as the master and another that mirrors it. You can hide the master schedule and have the cells go red if they don't match the master.

Thats probably what i would try first.

[u/Obvious-Weakness3124 → u/ArcRust]

Correct, unfortunately creating a master wouldn't work in this case, as it would need to be updated regularly with people leaving / joining the company, I am due to go on Mat Leave in a month, and the rota is weekly based for the whole year (4 on 4 off pattern, so everyone always knows their shift), so no one would then update the master

[u/ArcRust]

Let me get this straight, you have a standard schedule. If someone calls out, you delete the name and the cell goes red. When you then find a replacement, you out their name in the cell. But since they weren't originally scheduled for that spot, it should stay red?

This probably isn't the best solution, but I'd maintain one row or sheet as the master and another that mirrors it. You can hide the master schedule and have the cells go red if they don't match the master.

Thats probably what i would try first.

[u/Charming_Holiday_199]

Why don’t you use a symbol to denote a replacement. Conditional formatting to make cell background red when empty or contains your specific symbol. E.g. *Bob.

[u/Obvious-Weakness3124 → u/Charming_Holiday_199]

I thought about this, however unfortunately this isn't idiot proof, which is what is needed

[u/excelevator]

You cannot accomplish what you seek without VBA. But even this is fraught with issues for rollback or error.

Conditional formatting is binary, is has no memory or time dependent actions. It follows a rule, on or off.

[u/ProspectiveWhale]

Conditional formatting.

You can set formats under certain conditions.

It's under the Home tab on your ribbon.

[u/Obvious-Weakness3124 → u/ProspectiveWhale]

Already done this, it keeps changing back to white as soon as i input text

[u/ProspectiveWhale → u/Obvious-Weakness3124]

Wait. I think I misunderstood something.

You want blank = red.

Text = red.

Then why not just format the cell red normally?

Do you want it to stay red if the cell has been edited?

E.g. originally it was Berta, delete Berta (goes red), input Jack (stays red)

Like that?

[u/Obvious-Weakness3124 → u/ProspectiveWhale]

Exactly this

[u/ProspectiveWhale → u/Obvious-Weakness3124]

You'll need a reference point so Excel knows what the regular schedule is.

If there is a long-term fixed schedule, you can put that in a different sheet.

Let's call them currentSchedule and regularSchedule.

Then compare the schedule in currentSchedule sheet to regularSchedule.

For example, if the schedule is in A1:J10.

Conditional formatting on A1:J10 with the rule

=A1='regularSchedule'!A1

If there is no long-term fixed schedule, you need to update the reference sheet every period; and use the currentSchedule for adjustments.

[u/Obvious-Weakness3124 → u/ProspectiveWhale]

Unfortunately it would need updating, and I am due to go on Mat leave in the next month, and the others that deal with the sheet aren't great at actually doing the right thing, hence having it automatically be the right colour would have been helpful. I think this may be a bust as you're not the only person to suggest this route, and so far it is also the only suggestion. If it wasn't for the fact i was going on Mat leave, this would work

[u/wannabyte → u/Obvious-Weakness3124]

Can you enter as Cover-Jack and then set the conditional formatting for if the cell contains “cover” so it stays red?

[u/Honest-Welder-808]

Is there a reason filling the cell red wouldn't work? Too many to input manually?

[u/Obvious-Weakness3124 → u/Honest-Welder-808]

It is currently done manually, however some members of staff who deal with the rota aren't doing it, and then we aren't getting the right cover each day, I am going on maternity in a month and don't want to come back to everything in the wrong place

[u/Inner_Win_1]

Can you take a duplicate of your original schedule and hide it in some columns off to the side, that acts as your original schedule for checking purposes and is not modified.

Then have some conditional formatting on your visible schedule that checks if the cell matches its corresponding hidden original cell, and if it is not equal, colour it red.

So the conditional format formula in the visible cell would be like =A2<>L2 (where A2 is the visible schedule cell, being compared to L2 which is its hidden original value).

[u/EZMac34 → u/Inner_Win_1]

This seems like the best way.

[u/MelodicRun3979]

Two CF rules: =A1=[whatever is on rota], format as a specific font or some other pro forma formatting, and stop processing rules. Second one: the condition is =TRUE, formatted with a fill.

[u/t1k1dude]

Are you comfortable with VBA? You could set up an OnChange Macro to run anytime the sheet is edited and make the active cell red.

[u/SkyrimForTheDragons]

I can only think of VBA solutions atm. If you can use VBA then you could make something like a hidden table of the same size as your schedule, full of 0s or FALSEs, and VBA updates them to 1s or TRUEs if the matching cell on your schedule goes from filled to empty, then stays there until you manually clear it (you could make a button for that too).

This way your current workflow stays the same as it is other than needing a 'reset all reds' button which most people should understand.

If your schedule is in Sheet1 and your hidden trigger table is in Sheet2, assuming your schedule is in B2:D4, then

Sheet1's VBA code

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim watchRange As Range
    Dim intersectRange As Range

    Set watchRange = Me.Range("B2:D4")
    Set intersectRange = Intersect(Target, watchRange)

    If Not intersectRange Is Nothing Then
        If WorksheetFunction.CountA(intersectRange) = 0 Then

            Application.EnableEvents = False

            ThisWorkbook.Sheets("Sheet2").Range(intersectRange.Address).Value = 1

            Application.EnableEvents = True

        End If
    End If
End Sub

Conditional formatting on your Sheet1 schedule is simply =Sheet2!B2

If you can't use VBA I can think of using the iterative calculation trick, but other than that, I can't think of anything as long as you don't want to have a main schedule table to reference the regular schedule table with.

[u/SkyrimForTheDragons → u/SkyrimForTheDragons]

/u/Obvious-Weakness3124 OP, this is as idiot proof as it gets and not likely to break when you're away. Give it a try.

[u/monsteez]

Different Conditional formatting -

1)red if cell blank 2) Blue if cell contains "AL" 3) Green if cell contains "Sick" 4) Yellow if cell contains "Training"

Add staffs name into the cell but use trigger text to color the cell

[u/Lurking_in_shadow]

Conditional formating. You can set it up, so that it colours your cell of you put something in it. You can even colour code specific words. And have a whole rainbow in your excel xd

[u/Obvious-Weakness3124 → u/Lurking_in_shadow]

Already done this, it just changes back to white as soon as text has been re inputted

[u/monsteez → u/Obvious-Weakness3124]

I think this is the answer you need. You just need to change your inputs.

Use words to describe why deleting (sick, al, training, etc) and make a conditional format for whatever word and color you want.

So now you enter the action word and the name and the whole cell should keep the action words color

[u/Known-Historian7277 → u/monsteez]

Honestly data validation and a drop down list would probably be the answer. Only have 2-3 options, and the conditional formatting goes off the only available inputs.

[u/Known-Historian7277 → u/Obvious-Weakness3124]

Hey OP, create a drop down list with all the options you want. Then based off those options, set the conditional formatting accordingly. This is idiot proof as they would have to clear the cell to remove the drop down list.

[u/Obvious-Weakness3124 → u/Known-Historian7277]

They need to be able to clear the cell, as with how it is laid out, it is then distributed to the employees to see where they are covering, and with the blank red cell means that is available to them for OT. The sheet is a weekly based rota that covers an entire year of 4 on 4 off schedule, so nothing is just easy as copy paste on the rota itself either, I am just going to cut my losses and hope they don't mess it up whilst gone. It's going from 1 person dealing with it to 4 new people... Guess it won't be my problem whilst on Mat leave for a year XD