VBA Conditional Format Column Row Offset on Worksheet Change Event

[jhedges]

I have been working on this on and off, was put on the back burner until now. The previous thread is linked here - https://www.mrexcel.com/forum/excel...et-based-upon-cell-condition.html#post4958617.

The first part of the code is working fine. What I'm trying to accomplish now is to put the rows colored green back to their alternating colors if the word "CLOSED" is removed from the cell. The code below is where I'm at so far; however, it isn't putting the rows back to the original colors. Wondering is someone could help me out...?

Private Sub Worksheet_Change(ByVal Target As Range)
    If (Target.Row - 0) Mod 8 = 0 And Target.text = "CLOSED" Then
            Select Case UCase(Format(Target.offset(-2), "ddd"))
                Case "SAT"
                    Target.offset(4).Interior.Color = 5296274
                Case Else
                    Target.offset(1, 0).Resize(4, 3).Interior.Color = 5296274
            End Select
    Else
    If (Target.Row - 0) Mod 8 = 1 And Target.text <> "CLOSED" Then
            Select Case UCase(Format(Target.offset(-2), "ddd"))
               Case "SAT"
                    Target.offset(4).Interior.Color = 2
                Case Else
                    Target.offset(1, 0).Resize(4, 3).Interior.Color = 13759225
            End Select
     End If
    End If
End Sub

 

[AFPathfinder]

Are you stepping through the code to see if the branches are picking up the changes? I'm not sure if subtracting Target.Row by 0 is doing anything, but the modulus is only targeting rows divisible by 8 and (8)+1.

 

[jhedges]

This is what I have now. It is working as designed now. The code I had in up to this point was me just trying options. Is there a way to only look for the word CLOSED of if the cell is blank? Versus if there is other text in the cell? Maybe ask if you want to color the cells if no, then exit sub? I will try some options and post back if I can get it to work?

Private Sub Worksheet_Change(ByVal Target As Range)
    If (Target.Row - 1) Mod 8 = 0 And Target.text = "CLOSED" Then
            Select Case UCase(Format(Target.offset(-2), "ddd"))
                Case "SAT"
                    Target.offset(4).Interior.Color = 5296274
                Case Else
                    Target.offset(1, 0).Resize(4, 3).Interior.Color = 5296274
            End Select
    Else
    If (Target.Row - 1) Mod 8 = 0 And Target.text <> "CLOSED" Then
            Select Case UCase(Format(Target.offset(-2), "ddd"))
                Case "SAT"
                    Target.offset(4).Interior.Color = 16777215
                Case Else
                    Target.offset(1, 0).Resize(4, 3).Interior.Color = 13759225
                    Target.offset(2, 0).Resize(3, 3).Interior.Color = 16777215
                    Target.offset(3, 0).Resize(2, 3).Interior.Color = 13759225
                    Target.offset(4, 0).Resize(1, 3).Interior.Color = 16777215
            End Select
     End If
    End If
End Sub

 

[AFPathfinder]

You can do all those things. It just depends on what you decide to do. If you are checking the value of a single cell (Target) against several outcomes, the Select Case seems ideal to capture all the differences. It looks like you're pretty familiar with that, but just in case:

Select Case Target.Text
    Case ""
        'Do something here

    Case "CLOSED"
        'Do a different something here

    Case Else
        'Do something else with all other possibilities; catch all

End Select

If you're talking about checking a larger range for that value, you could use the Find function.

            On Error Resume Next

            x = Columns(Target.Column).Find(What:="CLOSED", After:=Cells(1, 1), _
                LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=True).Row

            On Error GoTo 0

 

[jhedges]

Thanks - this is what I came up with and it is working. I wanted to build in an option to make sure the user wanted to change the cell color. As sometimes they would not want to change the color of the cells when the worksheet_change event fired. Do you see anything that might need to be changed?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Response
    If (Target.Row - 1) Mod 8 = 0 And Target.text = "CLOSED" Then
            Select Case UCase(Format(Target.offset(-2), "ddd"))
                Case "SAT"
                    Target.offset(4).Interior.Color = 5296274
                Case Else
                    Target.offset(1, 0).Resize(4, 3).Interior.Color = 5296274
            End Select
            
    Else
    
    If (Target.Row - 1) Mod 8 = 0 And Target.text <> "CLOSED" Then
        Response = MsgBox("If cells are green, then do you want to return them back to their original color?", vbYesNo + vbQuestions, "Confirmation")
        
        If Response = vbYes Then
            Select Case UCase(Format(Target.offset(-2), "ddd"))
                Case "SAT"
                    Target.offset(4).Interior.Color = 16777215
                Case Else
                    Target.offset(1, 0).Resize(4, 3).Interior.Color = 13759225
                    Target.offset(2, 0).Resize(3, 3).Interior.Color = 16777215
                    Target.offset(3, 0).Resize(2, 3).Interior.Color = 13759225
                    Target.offset(4, 0).Resize(1, 3).Interior.Color = 16777215
            End Select
        End If
     End If
    End If
End Sub

 

[AFPathfinder]

Only some cosmetic and OCD type changes, really. It looks well written and fairly easy to follow.

The user may find the MsgBox annoying if they are changing those rows often. If that's the case, you might consider moving the macro a loop and button to allow the user to choose when they want to make the color changes.