Change row colour based on value...what am I doing wrong

[Zzzzzz]

This will teach me for not using my VBA skills for over a year!

I am taking the lazy route and by coming here first as I do own several of Mr. Excels publications but I am looking for the quickie.

I have a workbook with a worksheet that I use to track open cases. The code should turn the row from A:G red if the value in active cell "F" has a value of Open in it...it's working except it is turning 1 row below, red, and not the active row, but I don't have an offset?

Also on the reverse, if the value is Closed then it needs to remove the pattern

Selection.Interior.ColorIndex = xlNone

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    If Range("F" & ActiveCell.Row) = "Open" Then
        Range("A" & ActiveCell.Row & ":G" & ActiveCell.Row).Select
    End If
    
    With Selection.Interior
        .ColorIndex = 3
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
    End With
End Sub

 

[Norie]

Why don't you just use Conditional Formatting?

If you really need the code I suggest you use the Target object.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    If Range("F" & Target.Row) = "Open" Then
        With Range("A" & Target.Row).Resize(, 7).Interior
            .ColorIndex = 3
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
        End With
    End If
    
End Sub

 

[Zzzzzz]

Conditional Formatting

Hi Norie...thank you for the quick reply!

I had tried this but forgot how to select more than the active cell (, 7)...it's starting to come back now. Your example works well except if the value is "Closed" I want the pattern to remain blank.

I tried a variation of your code with an Else but I think my syntax is all wrong?

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    If Range("F" & Target.Row) = "Open" Then
        With Range("A" & Target.Row).Resize(, 7).Interior
            .ColorIndex = 3
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
        Else
        With Range("A" & Target.Row).Resize(, 7).Interior
            Selection.Interior.ColorIndex = xlNone
        End With
    End If
   
End Sub

 

[Norie]

You don't need the Selection and your Else is disrupting the With End With structure.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    If Range("F" & Target.Row) = "Open" Then
        With Range("A" & Target.Row).Resize(, 7).Interior
            .ColorIndex = 3
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
        End With
    Else
        With Range("A" & Target.Row).Resize(, 7).Interior
            .ColorIndex = xlNone
        End With
    End If
   
End Sub

 

[Zzzzzz]

Hey Norie, again thanks!

I see this now

You don't need the Selection and your Else is disrupting the With End With structure.

What if the user puts a value of "open" all lower case, how would you handle this, or is it better to restrict the values that can be input?

What is your opinion on this?

 

[Norie]

There are various ways you could deal with that.

One of them would be to use Data>Validation... with the List option to restrict user input.

Another would be to deal with it in code.

One way of doing that would be this.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    If UCase(Range("F" & Target.Row)) = "OPEN" Then
        With Range("A" & Target.Row).Resize(, 7).Interior
            .ColorIndex = 3
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
        End With
        Application.EnableEvents = False
        Range("F" & Target.Row) = "Open"
        Application.EnableEvents = True
    Else
        With Range("A" & Target.Row).Resize(, 7).Interior
            .ColorIndex = xlNone
        End With
    End If
   
End Sub

 

[Zzzzzz]

Now that is slick!

Hi Norie,

That is very slick…love it! I was thinking of going the drop down menu route or using masking if possible!

You're making me look good on this one!

Thank you very much.

Michael

 

[Zzzzzz]

Don't want to look a gift horse in the mouth however...

Hey Norie,

I don't mean to monopolise your time, but if I wanted to apply the same case-control over other cells, i.e. if someone inputs closed have the cell change to Closed, how is this done? I reviewed your code and can't understand how it is changing case to Open. I realize a detailed line-by-line explanation would be selfish of me to ask but can you give me the Coles notes version?

Hoping I can learn something instead of just copying!