Conditional Formatting Help Needed

I have my timesheet set up so that in column B the number 1 or 2 indicates whether or not it is overtime payment. What I am trying to acheive is to add a third trigger - the number 3 which will auto write the word Röd dag (Swedish version of bank holiday) in the relevant C column. The C column is used to manually enter the Start time of my shift so I cannot insert a formula there. Can this be done through conditional formatting?
I have attached a copy of my time sheet and left some entries under the Jan page to indicate better what I am wanting to do.
Any and all help will be appreciated.
Thanks
Alan

No it can not be done by conditional formatting. As the name indicates, confitional formatting changes the formatting of the cell. It does not change not the contents.
You could create a macro to change the contents. You can add this code to the ThisWorkbook module

Code:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim rInt As Range
Dim rcell As Range
'Don't run if UPS sheet (add others as needed)
If Sh.Name = "UPS" Then Exit Sub
Set rInt = Intersect(Target, Sh.Range("B11:B41"))
If Not rInt Is Nothing Then
For Each rcell In rInt
If rcell = 3 Then
rcell.Offset(0, 1) = "Röd dag"
End If
Next
End If
End Sub

If the sheet name being changed is "UPS" it exits. So if any of the other sheets, it checks if B11:B41 has been changed, if any of those have been changed and the value is a 3 then it adds "Röd dag" to the cell to the immediate right of the cell (ie Col C).

Adding the 2 lines in blue will remove "Röd dag" if the user changes the 3 to a "1" or a "2" instead of having to manually remove it.

Code:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim rInt As Range
Dim rcell As Range
'Don't run if UPS sheet (add others as needed)
If Sh.Name = "UPS" Then Exit Sub
Set rInt = Intersect(Target, Sh.Range("B11:B41"))
If Not rInt Is Nothing Then
For Each rcell In rInt
If rcell = 3 Then
rcell.Offset(0, 1) = "Röd dag"
Else:
rcell.Offset(0, 1) = ""
End If
Next
End If
End Sub

I had placed a numerical value of 2 above the empty cells and a value of 4 below the empty cells and averaged the column. All of the empty cells had no effect on the result of 3.

In summary, they all produced empty cells with zero lengths, had no effect as a precedent for formulas, and there was no change in the formatting of the cells. It concludes that in this instance of the code in post #4,

Else:
rcell.Offset(0, 1) = ""

it makes no difference whether the cell is set to "" or to use the .ClearContents method. If there may be an advantage I am overlooking, please post.

From your results, it seems that excel may have changed the way it uses a null string. I presume the earlier versions work as they did in the past. In earlier versions of excel A null adds a zero length string to a cell so the cell will not be blank / empty. Clear contents makes the cell blank / empty. For compatibilty and to be explicit in the what the intent is for the code, I would still recommend using ClearContents

Also there is a reason for NOT including the ELSE at all in the code. If the user adds the date to col C BEFORE setting the 1/2 in B, they would not want it to be cleared.
It may be more appropriate to use:
Else
If rcell.Offset(0, 1) = "Röd dag" then rcell.offset(0,1).ClearContents

Adding the 2 lines in blue will remove "Röd dag" if the user changes the 3 to a "1" or a "2" instead of having to manually remove it.

Code:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim rInt As Range
Dim rcell As Range
'Don't run if UPS sheet (add others as needed)
If Sh.Name = "UPS" Then Exit Sub
Set rInt = Intersect(Target, Sh.Range("B11:B41"))
If Not rInt Is Nothing Then
For Each rcell In rInt
If rcell = 3 Then
rcell.Offset(0, 1) = "Röd dag"
Else:
rcell.Offset(0, 1) = ""
End If
Next
End If
End Sub

Just as an after thought and not terribly important but is it possible to make the word Röd dag text colour red?
As I say its just as easy for me to unprotect the sheet and change the colour manually but wondered if it could be automated.

Using conditional formatting for the cells C11 to C41 just dosnt seem to work, if I use just cell 11, as long as it is a Röd dag in that cell the others will format but not if I try formatting 11 to 41.
I am confused......

Alan,
1. Click on Cell C11 then on the ribbon Home> Conditional Formatting> Manage Rules> New Rule.
2. Set up the rule as the image shows in my above post then click OK.
3. In the manager window that now shows, you will see your new rule under your existing rule. Click on the reference icon to the right on the AppliesTo column of the new rule.
4. The window will minimize to a reference window. Highlight cells C11 to C41. The range will automatically be paced in the cell reference line.
5. Click the return icon to the right. You will return to the Conditional Formatting Manage Window.
6. Make sure that "Stop if True" is checked next to your first rule.

Note: you could have also typed in =$C$11:$C$41 in the Applies To box. Click Apply