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

Zzzzzz

Board Regular
Joined
Oct 5, 2004
Messages
206
This will teach me for not using my VBA skills for over a year! :eek:

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
Code:
Selection.Interior.ColorIndex = xlNone

Code:
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
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Why don't you just use Conditional Formatting?

If you really need the code I suggest you use the Target object.
Code:
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

Board Regular
Joined
Oct 5, 2004
Messages
206
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?
Code:
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
You don't need the Selection and your Else is disrupting the With End With structure.
Code:
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

Board Regular
Joined
Oct 5, 2004
Messages
206

ADVERTISEMENT

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

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
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.
Code:
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

Board Regular
Joined
Oct 5, 2004
Messages
206
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

Board Regular
Joined
Oct 5, 2004
Messages
206
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!
 

Forum statistics

Threads
1,136,266
Messages
5,674,725
Members
419,522
Latest member
bizhani

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top