Issue with coloring rules

[Revoo]

Greetings!

At work we have a schedule with colors and stuff. Today we are doing everything manually and I would like to automize a few things.

Below you'll find a small cut of the schedule. We are around 8 people in total.

To clarify how it works... the letters "D, M & K" are "Day, Morning and Night" shifts. Those are finished. What I need help with is the Dispatcher coloring. We have 3 diffrent kinds that work the same...

I made 2 examples. On Adams schedule is how it would be right now. On Bills row is how we would want it to be.
So when I type "Dispatcher" on row 8 we would want it to color all the way like it does on row 13 AND at the same time make the friday the week after Black with white text saying "Off" (Because you get the next friday off when you have dispatcher the week before).


We also have 2 other weekly roles that work pretty much the same but that I can adjust if I get some help with the Dispatcher function.



If I sound very confusing I'll summarize this:

When we type "Dispatcher" in the first cell we would want all the cells for the rest of that week to turn yellow and also the friday the next week to turn black with white text saying "off".


I hope this is possible! This rule will be applied to each person but I'm sure I can handle that myself. If any of you knows how to do this I would be very greatful!

 

[DRSteele]

Select Cells B8 to H8. Invoke the Conditional Formatting dialogue box. Select New Rule | Use a formula...| and paste this exactly in the Format value where... box =$B$8="dispatcher" and then set the format to yellow fill.

For M7 you will have to write a formula that determines whether the cell should display "Off" or one of "M" "D" or "K". You cannot use Conditional Formatting to report a text or value.

For the CondFrmt, select Cell M7. Invoke the Conditional Formatting dialogue box. Select New Rule | Use a formula...| and paste this exactly in the Format value where... box =$B$8="dispatcher" and then set the format to black fill and white font.

Är det klart?

 

[Rick Rothstein]

You could also do what you want using event code...

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address <> "$1:$" & Rows.Count Then
    If Target.Column = 2 And Target.Value = "Dispatcher" Then
      Target.Resize(, 7).Interior.Color = vbYellow
      With Target.Offset(, 11)
        .Value = "Off"
        .Interior.Color = vbBlack
        .Font.Color = vbWhite
      End With
    End If
  End If
End Sub

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself. Note... if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.