Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Star Lounger AlanWade's Avatar
    Join Date
    Dec 2009
    Location
    Sweden
    Posts
    86
    Thanks
    9
    Thanked 1 Time in 1 Post

    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
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    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).

    Steve

  3. The Following User Says Thank You to sdckapr For This Useful Post:

    AlanWade (2014-04-18)

  4. #3
    Star Lounger AlanWade's Avatar
    Join Date
    Dec 2009
    Location
    Sweden
    Posts
    86
    Thanks
    9
    Thanked 1 Time in 1 Post
    Thanks Steve, does exactly what I wanted it too

    PS appologies, I forgot to unprotect the workbook, not that it made any difference but the password was alskling
    Last edited by AlanWade; 2014-04-18 at 06:13.

  5. #4
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,349
    Thanks
    48
    Thanked 273 Times in 251 Posts
    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

  6. The Following User Says Thank You to Maudibe For This Useful Post:

    AlanWade (2014-04-19)

  7. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    That is a good idea. If desired, I would recommend using
    rcell.Offset(0, 1).ClearContents

    to clear the cell's content rather than adding a null string

    Steve

  8. #6
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,349
    Thanks
    48
    Thanked 273 Times in 251 Posts
    Sparking my curiosity with your remark, I have tested NULL, "", .ClearContents, and Blank against each other using the len() and IsBlank() formulas. Consider the following codes

    Code:
    Public Sub LenNULL()
    Range("B2") = Null
    Range("C2") = "Length of B2 = " & Len(Range("B2"))
    End Sub
    
    Public Sub LenDoubleQuotes()
    Range("B6") = ""
    Range("C6") = "Length of B6 = " & Len(Range("B6"))
    End Sub
    
    Public Sub LenClearContents()
    Range("B10").ClearContents
    Range("C10") = "Length of B10 = " & Len(Range("B10"))
    End Sub
    
    Public Sub LenBlank()
    Range("B14") = blank
    Range("C14") = "Length of B14 = " & Len(Range("B14"))
    End Sub
    
    Public Sub IsEmptyNULL()
    Range("B18") = Null
    Range("C18") = "IsEmpty(B18) = " & IsEmpty(Range("B18"))
    End Sub
    
    Public Sub IsEmptyDoubleQuotes()
    Range("B22") = ""
    Range("C22") = "IsEmpty(B22) = " & IsEmpty(Range("B22"))
    End Sub
    
    Public Sub IsEmptyClearContents()
    Range("B26").ClearContents
    Range("C26") = "IsEmpty(B26) = " & IsEmpty(Range("B26"))
    End Sub
    
    Public Sub IsEmptyBlank()
    Range("B30") = blank
    Range("C30") = "IsEmpty(B30) = " & IsEmpty(Range("B30"))
    End Sub
    A Copy of each code was place in Column A. The outputted Value when run was sent to column B which all yielded empty cells. The test results were sent to column C.

    NULL3.png

    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.
    Last edited by Maudibe; 2014-04-19 at 06:49.

  9. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    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

    Steve

  10. #8
    Star Lounger AlanWade's Avatar
    Join Date
    Dec 2009
    Location
    Sweden
    Posts
    86
    Thanks
    9
    Thanked 1 Time in 1 Post
    Quote Originally Posted by Maudibe View Post
    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
    Thanks Maud, that just finishes off my timesheet.

  11. #9
    Star Lounger AlanWade's Avatar
    Join Date
    Dec 2009
    Location
    Sweden
    Posts
    86
    Thanks
    9
    Thanked 1 Time in 1 Post
    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.

  12. #10
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,349
    Thanks
    48
    Thanked 273 Times in 251 Posts
    Alan,

    You can apply conditional formatting. If the value is Röd dag then font is red. Apply the formula to the cells in column C.

    HTH,
    Maud

    conditional.png

  13. #11
    Bronze Lounger
    Join Date
    Mar 2002
    Location
    Newcastle, UK
    Posts
    1,525
    Thanks
    32
    Thanked 180 Times in 174 Posts
    Hi

    ..you could use something like:
    rcell.Offset(0, 1).Font.ColorIndex = 3

    ..where 3 will give you Red

    zeddy

  14. #12
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,349
    Thanks
    48
    Thanked 273 Times in 251 Posts
    Zeddy,

    Using you line of thought, there will need to be a similar process to make the font black if it is not Röd dag.

  15. #13
    Bronze Lounger
    Join Date
    Mar 2002
    Location
    Newcastle, UK
    Posts
    1,525
    Thanks
    32
    Thanked 180 Times in 174 Posts
    Hi Maudibe

    ..heh heh. I knew you knew that.
    ..I was waiting for Alan to discover that Font.ColorIndex = 1 is for black.

    zeddy

  16. #14
    Star Lounger AlanWade's Avatar
    Join Date
    Dec 2009
    Location
    Sweden
    Posts
    86
    Thanks
    9
    Thanked 1 Time in 1 Post
    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......
    Last edited by AlanWade; 2014-04-20 at 15:51.

  17. #15
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,349
    Thanks
    48
    Thanked 273 Times in 251 Posts
    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

    conditional2.png

  18. The Following User Says Thank You to Maudibe For This Useful Post:

    AlanWade (2014-04-21)

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •